Small macro problem

Dim rng As Range
     Set rng = Range("g17").End(xlDown).Resize(1, 1)

       rng.AutoFill Destination:=rng.Resize(rng.Columns.Count, 2)


    What I want the above to do,  is to go to the last item in Column G (say
G5) and copy it to the cell below (G6), but this fills both G6 and H7.

I feel sure it is very easy to resolve but I seem to have tried all
permutations without success.

Please advise where I am going wrong.


Bob


0
bob2381 (34)
9/16/2003 9:12:49 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
484 Views

Similar Articles

[PageSpeed] 15

Switch your Row/Column values in .Resize():

    Dim rng As Range
    Set rng = Range("G17").End(xlDown)
    rng.AutoFill Destination:=rng.Resize(2, 1)

Your code as written should have filled H5, not G6 and H7, right?


In article <l1L9b.12359$SL6.108487911@news-text.cableinet.net>,
 "Robert Gillard" <bob@mystical.demon.co.uk> wrote:

> Dim rng As Range
>      Set rng = Range("g17").End(xlDown).Resize(1, 1)
> 
>        rng.AutoFill Destination:=rng.Resize(rng.Columns.Count, 2)
> 
> 
>     What I want the above to do,  is to go to the last item in Column G (say
> G5) and copy it to the cell below (G6), but this fills both G6 and H7.
> 
> I feel sure it is very easy to resolve but I seem to have tried all
> permutations without success.
> 
> Please advise where I am going wrong.
> 
> 
> Bob
> 
>
0
jemcgimpsey (6723)
9/16/2003 9:28:53 PM
Hi Bob,

Try this:

    Dim rng         As Range
    Dim rngLast     As Range
    
    Set rng = wks.Range("G1")
    Set rngLast = Cells(Rows.Count, rng.Column).End(xlUp)
    
    rngLast.Offset(1, 0).Value = rngLast.Value

I hope this helps.

Regards,
James S

>-----Original Message-----
>Dim rng As Range
>     Set rng = Range("g17").End(xlDown).Resize(1, 1)
>
>       rng.AutoFill Destination:=rng.Resize
(rng.Columns.Count, 2)
>
>
>    What I want the above to do,  is to go to the last 
item in Column G (say
>G5) and copy it to the cell below (G6), but this fills 
both G6 and H7.
>
>I feel sure it is very easy to resolve but I seem to have 
tried all
>permutations without success.
>
>Please advise where I am going wrong.
>
>
>Bob
0
9/16/2003 9:43:29 PM
Reply:

Similar Artilces:

Here is fix for Windows file problems
If you have installed a Windows update and started having problems opening, closing, copying files, see: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.excel.misc&mid=1fe358b1-c8eb-4217-ad2a-4b9fcf741916 A better solution is offered at http://support.microsoft.com/kb/918165 "Problems in Windows Explorer or the Windows shell after you install security update MS06-015". Patricia Shannon wrote: > If you have installed a Windows update and started having problems opening, > closing, copying files, see: > > http://ww...

Problem with CFTPConnection
Hi I've an application that connects to an FTP Server and uploads a big directory (size over 500 MB). Now what happens is that after sometime the application stops uploading files and stays idle. The FTP server connection timeout value is set to 2 minutes. So the server seeing the connection idle for two seconds, disconnect the session and no more file is uploaded. On debugging I found out that after some time CFTPConnection.PutFile() method hangs on some random file and stays like that for 5 minutes (default send timeout value of CFTPSession). During this time the server connection timeo...

Excel 97 Win XP calc problem?
I have a machine running office 97 and Win XP. I have seen posts stating it runs fine, but I don't know how man people use spreadsheets that require a hard calculation: Excel will not force a calculation. i.e. ctrl-alt-f9 does not cause full workbook calculation as required. Meaning cells do not update and it doesn't calculate unless you clic in the cell to edit and hit enter. neither does the "Calc Now" button. Auto or manual calculation mode has no effect. Anybody else have this problem -- Message posted from http://www.ExcelForum.com Make sure that both the SR1 a...

Formatting a subtotal line with a macro
I already have in use a macro that runs a sort and subtutal function. I would like to format the subtotal row using visual basic since there is already a macro in place. Hi Look at this: ActiveCell.Font.Bold = True Regards, Per "DJDKAL" <daniel.kalfayan@hotmail.com> skrev i meddelelsen news:A86040B0-B4C4-4E11-A80C-E80132C9710F@microsoft.com... >I already have in use a macro that runs a sort and subtutal function. I > would like to format the subtotal row using visual basic since there is > already a macro in place. This is what I've g...

CRM Outlook Client Configuration Wizard Problem
CRM 4.0 - Microsoft Outlook Client Guys, We've Microsoft CRM 4.0 Server roll up 8 as well as CRM Outlook Client on the same roll up version. Our Internal Web Access works without problems users get to it and they're automatically authenticated. IFD Development is the same way except they're prompt through a login page for their username and password when they're coming from an external network. Ever since we moved to a new facility users are experiencing problems connecting to CRM using their outlook client, I have a test machine where I have the CRM Outlook Client ins...

VB Macro #2
I have a macro that is generating Excel files. When the macro runs, it saves two different tabs in the workbooks as values. It needs to test and wait until the formulas that are pulling amounts from another database are correctly populated with the amounts. Right now, I am getting N/A# instead of amounts for some, but not all, of the files. I think the problem is that the formulas have not completed pulling the amounts before the macro pastes that tab as zeros. (N/A# is what appears when the formulas have not completed calculating - once they have completed, the N/A# is replaced ...

Problem with Update function of CRecordSet class
Hi All When I use from Update function of CRecordSet class, it apply to table after a few time(for example 1 second) when I need to see it at time. Thanks ...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

request to solve my problem #5
hello friends this is vijay,i got a problem while doing my project based on vc++ (i.e)"How to implement Fit Window feature in VC++6.0 in an MDI Application" if any of you got any idea about that please do reply me with the proceedure.i will be waiting for your reply bye with regards vijayprasadreddy ...

Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the cell length. I wish to keep the cell lenghts for the entire document, but do wish for teh text in that particular row to be displayed. How about if you select that cell, then format|cells|alignment tab|check wrap text And with that row selected format|row|autofit SHR77 wrote: > > As I type text in some cells, it does not always dispaly if it exceeds the > cell length. I wish to keep the cell lenghts for the entire document, but do > wish for teh text in that particular row to be displayed. -- Da...

Macros not performing correctly
I recorded a macro to place a vlookup formula in four cells that are side by side. When I run the macro it only performs the first task, it does not go on to do the other three. I used the tab key to go to the next cell while recording the macro, I don't know if that's why it's not working or if it's something else. The script looks like this: Sub EERates() ' ' EERates Macro ' Macro recorded 3/4/2005 by BCBSOK ' ' ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet1!R3C1:R102C2,2,0)" Range("F15").Select ActiveCell.FormulaR1C...

Excel List Sorting Problem (Descending)
Hi there, I'm having trouble sorting my list--my column contains *only* 4-digit numbers but when I click on "descending order", only about the first half of the rows are arranged this way, before it begins again to arrange the rest in descending order. Like this: 5120 5119 5118 4000 3050 5116 4112 etc. Has this problem happened for anybody else? I'd appreciate any help you can offer. Part of your list is text, although it looks like numbers. Format an empty cell as number. Enter the number 1. Copy. Select your "numbers". Edit>Paste Special, check Mul...

I really need help with a formview problem
I have a formview that I place a toolbar onto. In my OnInitialUpdate(), after I create the toolbar, I say GetParentFrame()->RecalcLayout(); ResizeParentToFit(FALSE); ResizeParentToFit(TRUE); The formview is still not sized properly. It looks like the problem is the toolbar on the top of the formview. It is not being taken into account in sizing the formview. The difference at the bottom of the form that is not displayed is the size of the toolbar. Any suggestions? Should work if your toolbar was created (and docked) back in your CMainFrame::OnCreate() method. ----- William Gower ...

Printing problem in booklet format
When I try to print my booklet document, on the screen it looks perfect, but when i go into print preview it only has half of each page on the paper. When i print, it comes out exactly like that. I have Publisher 2003 and run it on XP home. My printer is an Epson stylus Photo RX650. I contacted Epson and they say its a Publisher problem. Any suggestions. Driver? http://tech.epson.com.au/downloads/product.asp?sCategory=Multi_Functional&id=stylusphotorx650&FileType=1&EmailAdd=&MetricIDReturned=624915&platform=winxp Are you selecting booklet in page setup? Do you ...

problem opening up program/file
Hello, All. I am having some trouble opening up Word. It was working okay until I loaded some fonts to FontBook. Okay, now I removed all my fonts but I still get this error message about corrupt fonts I have on my computer. When I open Word, I see the blue starting-up window: Word:Mac 2004. Right underneath the product ID #, I see that it is initiating... when it gets to the point "Optimizing font menu performance" I start getting this error windows popping up one after another. (E.g. The font M TimesSmallText is corrupt and should be removed.) I keep on clicking okay for a whole lot...

Money deluxe 2007 password problem
When I update my bank account transactions I get a message saying that my account was not updated because my password is incorrect. I then go into account info and have to update my password. I save the update, but then it happens again sporadically when I open Money at later times. Is there a way to fix this glitch? ...

printing problems with memorized reports
We have recently been running into printing problems with our memorized reports. We will print preview a report and it will look fine on screen, then we will send to the printer, and the report print extremely small in the corner of the page. However if we go back in and re-print the report then it will print normal size on the page. We are not making any settings changes at all, and when it does this appears to just be at random. We are printing to an HP laserjet 4000 series printer that is on our network. We've used RMS with the same configuration for approx. 5 years now and ...

Reports and sub-reports problems
Dear Sirs,I have a report with 4 sub-reports.How can it be that 2 sub-reports are showing the data triple.Can someone help?ThanksKlaus I just realized. It's not due to being a subreport - it's already showing on the normal report triple data. It was not before and I did not change anything. I hope that someone can help me.Thanks Klaus"Amateur" wrote:> Dear Sirs,> I have a report with 4 sub-reports.> How can it be that 2 sub-reports are showing the data triple.> Can someone help?> Thanks> Klaus...

connection problems
a popup saying internet explorer cannot diaplay webpage and it is a connection problem -- eummer2010 how does your problem/question pertain to windows update? terri wrote: > a popup saying internet explorer cannot diaplay webpage and it is a > connection problem Hello Terry, Would be nice if you could tell us more information, like what OS you're using, Service Pack, what other security software are you running, if any? etc. IE6 ? IE7 ? IE8 ? Repoяt problems and check for solutions automatically <http://windowshelp.microsoft.com/Windows/en-US/Help/...

There is a problem with your printer settings.
There is a problem with your printer settings. Your printer has given Publisher an invalid value, and the printer could not be correctly initialized. Check your printer settings in the Windows control panel." 1. I only have one network printer 2. The network printer is not the default printer 3. I have deleted the printer, rebooted and reinstalled the latest printer drivers. 4. The printer name is less than 8 characters long. ... any idea what the problem is??? -- kirks What version Publisher? Do you have the service packs? http://www.microsoft.com/downloads/search.aspx?displaylan...

Automated starting of app
Hi NG, I don't know if this a problem special to MFC or something else. I work with XP and VC++ 6.0. I wrote a small dialog based app which can collect names of files and/or directories. I can start this app and select the files and directories with the usual common dialogs - works fine. I can also mark the files in the explorer and dragdrop them to the icon of the app. In this case I get the names as argv[n] and can fill the dialog with them while the program is starting. Works fine too. But when I select too many file names to dragdrop them to the icon, while the program starts I get the...

problem with dependency on atl71.dll
Hello, I have problem removing dependency on atl71.dll. I've tried these: defining /D "_ATL_STATIC_REGISTRY" and Properties->Config->General->UseOfATL : Static Link To ATL din't helped, still asking for missing dll when trying register my dll on Win2k. When I supply atl71.dll it's fine, but not sure if this is safe solution. can somebody advise on that? thanks Post this in ATL group instead (or atleast cross-post) --- Ajay ...

Business Portal 4.0 Problems
Hi everyone, I was wondering if anyone here who has experience in Business Portal could help me out here. I have Installed Business Portal 4.0 on our SharePoint Services (WSS 3.0) and it seems to be running fine. But when I try to remove a user from the Business Portal list, I get this error: System.Exception: soap:Server Server was unable to process request --> The subscriber type in the subscription is invalid. PublisherAssemblyName: Microsoft.BusinessFramework PublisherClass: Microsoft.BusinessFramework.User PublisherEvent: Deleting SubscriberAssemblyName: Microsoft.Dynamics.R...

Header and Footer Macro
Hi! I created a Macro that inserts the path file, name, and date but I want to automate the authors name in their too. I remember someone inserting a msgbox that allowed the author to put in his/her name and it would show in the footer. As of right now this is what the Macro looks like: How do I also insert the authors name? Sub Traveler_Chart() ' ' Traveler_Chart Macro ' Macro recorded 10/31/2005 by David Huang 6-8395 ' ' With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSe...