autofill won't fill to end of used range

I am writing a macro to be used on a generated report.   As part of the 
macro, I need it to autofill formulas in various columns to the last row of 
data, which can vary each time the report is generated.  The first formula to 
be filled is in col D.  There are NO empty cells in column C (nor A & B for 
that matter) in the used range (3000+ entries), but when the macro runs it 
will only fill to row 605!  However, when I insert a breakpoint just before 
the formula autofills and manually double-click the hande to perform an 
autofill, it works just fine.  Needless to say, I don't want the user to have 
to do this. 

I can't find anything on the web addressing this issue.  Does anyone have 
any ideas?

Here is this portion of the macro:

Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).row
'Through out my macro, I need it to fill down based on col A, not the 
preceding column

Columns("D:D").Select
    Selection.ClearContents
    Range("d1").Value = "Season"
    Range("d2").FormulaR1C1 = "=Personal.xlsb!season(rc[-1])"
    Range("d2").Select
    Selection.Copy
    Selection.AutoFill Range("d2:d" & lastrow)


Thanks,
Nikki



0
Utf
1/17/2010 8:24:01 PM
excel 39879 articles. 2 followers. Follow

3 Replies
1765 Views

Similar Articles

[PageSpeed] 35

>'Range("d2").FormulaR1C1 = "=Personal.xlsb!season(rc[-1])"
  Range("d2").FormulaR1C1 = "=[filename.xls]sheetname!rc[-1]"

I would have written it like this. NO SELECTIONS
Sub testautofill()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
With Range("d2") 'Only one dot . in front of next 4 lines
  .EntireColumn.ClearContents
  .Offset(-1) = "Season"
  .Formula = "=[filename.xls]sheetname!rc[-1]"
  .AutoFill Range("d2:d" & lastrow)
End With
End Sub

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"NikkiS" <NikkiS@discussions.microsoft.com> wrote in message 
news:563BCD51-E8CD-473A-9158-24FAF228F818@microsoft.com...
>I am writing a macro to be used on a generated report.   As part of the
> macro, I need it to autofill formulas in various columns to the last row 
> of
> data, which can vary each time the report is generated.  The first formula 
> to
> be filled is in col D.  There are NO empty cells in column C (nor A & B 
> for
> that matter) in the used range (3000+ entries), but when the macro runs it
> will only fill to row 605!  However, when I insert a breakpoint just 
> before
> the formula autofills and manually double-click the hande to perform an
> autofill, it works just fine.  Needless to say, I don't want the user to 
> have
> to do this.
>
> I can't find anything on the web addressing this issue.  Does anyone have
> any ideas?
>
> Here is this portion of the macro:
>
> Dim lastrow As Long
> lastrow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).row
> 'Through out my macro, I need it to fill down based on col A, not the
> preceding column
>
> Columns("D:D").Select
>    Selection.ClearContents
>    Range("d1").Value = "Season"
>    Range("d2").FormulaR1C1 = "=Personal.xlsb!season(rc[-1])"
>    Range("d2").Select
>    Selection.Copy
>    Selection.AutoFill Range("d2:d" & lastrow)
>
>
> Thanks,
> Nikki
>
>
> 

0
Don
1/18/2010 1:35:00 PM
Don,

What I would find most useful is if you pointed out where Nikki's code went 
wrong.

There are probably dozens of ways to get the 'right' answer.  Knowing what 
line or lines of code prevented the code from producing the correct results 
would be very valuable.

mike

"Don Guillett" wrote:

> >'Range("d2").FormulaR1C1 = "=Personal.xlsb!season(rc[-1])"
>   Range("d2").FormulaR1C1 = "=[filename.xls]sheetname!rc[-1]"
> 
> I would have written it like this. NO SELECTIONS
> Sub testautofill()
> Dim lastrow As Long
> lastrow = Cells(Rows.Count, "a").End(xlUp).Row
> With Range("d2") 'Only one dot . in front of next 4 lines
>   .EntireColumn.ClearContents
>   .Offset(-1) = "Season"
>   .Formula = "=[filename.xls]sheetname!rc[-1]"
>   .AutoFill Range("d2:d" & lastrow)
> End With
> End Sub
> 
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett@gmail.com
> "NikkiS" <NikkiS@discussions.microsoft.com> wrote in message 
> news:563BCD51-E8CD-473A-9158-24FAF228F818@microsoft.com...
> >I am writing a macro to be used on a generated report.   As part of the
> > macro, I need it to autofill formulas in various columns to the last row 
> > of
> > data, which can vary each time the report is generated.  The first formula 
> > to
> > be filled is in col D.  There are NO empty cells in column C (nor A & B 
> > for
> > that matter) in the used range (3000+ entries), but when the macro runs it
> > will only fill to row 605!  However, when I insert a breakpoint just 
> > before
> > the formula autofills and manually double-click the hande to perform an
> > autofill, it works just fine.  Needless to say, I don't want the user to 
> > have
> > to do this.
> >
> > I can't find anything on the web addressing this issue.  Does anyone have
> > any ideas?
> >
> > Here is this portion of the macro:
> >
> > Dim lastrow As Long
> > lastrow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).row
> > 'Through out my macro, I need it to fill down based on col A, not the
> > preceding column
> >
> > Columns("D:D").Select
> >    Selection.ClearContents
> >    Range("d1").Value = "Season"
> >    Range("d2").FormulaR1C1 = "=Personal.xlsb!season(rc[-1])"
> >    Range("d2").Select
> >    Selection.Copy
> >    Selection.AutoFill Range("d2:d" & lastrow)
> >
> >
> > Thanks,
> > Nikki
> >
> >
> > 
> 
> .
> 
0
Utf
1/22/2010 10:53:01 PM
I did...

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"madelca100" <madelca100@discussions.microsoft.com> wrote in message 
news:51C6BAE5-B74F-4F3D-BB8B-F5DD9C015358@microsoft.com...
> Don,
>
> What I would find most useful is if you pointed out where Nikki's code 
> went
> wrong.
>
> There are probably dozens of ways to get the 'right' answer.  Knowing what
> line or lines of code prevented the code from producing the correct 
> results
> would be very valuable.
>
> mike
>
> "Don Guillett" wrote:
>
>> >'Range("d2").FormulaR1C1 = "=Personal.xlsb!season(rc[-1])"
>>   Range("d2").FormulaR1C1 = "=[filename.xls]sheetname!rc[-1]"
>>
>> I would have written it like this. NO SELECTIONS
>> Sub testautofill()
>> Dim lastrow As Long
>> lastrow = Cells(Rows.Count, "a").End(xlUp).Row
>> With Range("d2") 'Only one dot . in front of next 4 lines
>>   .EntireColumn.ClearContents
>>   .Offset(-1) = "Season"
>>   .Formula = "=[filename.xls]sheetname!rc[-1]"
>>   .AutoFill Range("d2:d" & lastrow)
>> End With
>> End Sub
>>
>> -- 
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett@gmail.com
>> "NikkiS" <NikkiS@discussions.microsoft.com> wrote in message
>> news:563BCD51-E8CD-473A-9158-24FAF228F818@microsoft.com...
>> >I am writing a macro to be used on a generated report.   As part of the
>> > macro, I need it to autofill formulas in various columns to the last 
>> > row
>> > of
>> > data, which can vary each time the report is generated.  The first 
>> > formula
>> > to
>> > be filled is in col D.  There are NO empty cells in column C (nor A & B
>> > for
>> > that matter) in the used range (3000+ entries), but when the macro runs 
>> > it
>> > will only fill to row 605!  However, when I insert a breakpoint just
>> > before
>> > the formula autofills and manually double-click the hande to perform an
>> > autofill, it works just fine.  Needless to say, I don't want the user 
>> > to
>> > have
>> > to do this.
>> >
>> > I can't find anything on the web addressing this issue.  Does anyone 
>> > have
>> > any ideas?
>> >
>> > Here is this portion of the macro:
>> >
>> > Dim lastrow As Long
>> > lastrow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).row
>> > 'Through out my macro, I need it to fill down based on col A, not the
>> > preceding column
>> >
>> > Columns("D:D").Select
>> >    Selection.ClearContents
>> >    Range("d1").Value = "Season"
>> >    Range("d2").FormulaR1C1 = "=Personal.xlsb!season(rc[-1])"
>> >    Range("d2").Select
>> >    Selection.Copy
>> >    Selection.AutoFill Range("d2:d" & lastrow)
>> >
>> >
>> > Thanks,
>> > Nikki
>> >
>> >
>> >
>>
>> .
>> 

0
Don
1/23/2010 12:08:30 AM
Reply:

Similar Artilces:

Autofill Formulas
Hello. I have a workbook with 31 sheets of data. Each sheet is a form with a record of data. I would like extract the data into a list. For example, range A5 is the same field on each sheet, so I want to autofill a formula reference the same cell on different sheets. I suspect I need a macro for this, but wanted to make sure first. Can you autofill like this =Sheet1!$A$5, =Sheet2!$A$5, =Sheet3!$A$5? -- Thanks, Mike =INDIRECT("Sheet" & (ROW()) & "!A5") Entered in A1 of new sheet. Copy down to A31............will increment the Sheet numbe...

Using Contacts as my address book does not display the names correctly
Hi I have my Contacts folder set as the address book which allows me to address email messages. My Contacts folder is carefully arranged to display as Surname Firstname. But when the email accesses the Contacts list, they are displayed Firstname Surname. I need to see Surnames in alpha order when I'm addressing an email, but I can't seem to get it to show them that way. Can anyone help please? Thanks, Change that here in OL2003: Tools menu > Email accounts > View/Change existing directories/address books > Click Next > Highlight Outlook Address Book > Clic...

Removing range names in formulae
If I have a spreadhseet with a lot of formulae that use range names (eg Apples + Pears + Oranges), is there a simple way to change all the range names back to the cell referencs to which they refer (eg B7 + A5 + C3? Thanks Jim Rech offered this solution: http://groups.google.com/groups?threadm=u3ZAo%23FmAHA.2048%40tkmsftngp03 Gary Baker wrote: > > If I have a spreadhseet with a lot of formulae that use > range names (eg Apples + Pears + Oranges), is there a > simple way to change all the range names back to the cell > referencs to which they refer (eg B7 + A5 + C3? > ...

Journal
I am using Outlook 2002/XP. I use journaling to keep log hours I have spent on different projects over the weeks. I work on different projects and every day I log entries in the journal (with an Entry Type of Task, Meeting or whatever; and I use the Company field for the Project name; and I key in the number of hours spent in the Duration field). What I would like is a sort of report or view where by given a start and end date the outlook would provide me with consolidated figures (for example, to get total number of hours spent on each project in a given month) Any ideas about...

OWA Front end doesnt work after SP2
After a little testing it was determined that if you try to access you OWA account through the Front End server it does not work properly. If you log in to Servername/exchange then all is fine. Any ideas would be great. Thanks Heith wrote: > After a little testing it was determined that if you try to access you OWA > account through the Front End server it does not work properly. If you log > in to Servername/exchange then all is fine. > > Any ideas would be great. > > Thanks > Heith, It doesn't help if you keep repeating your question with a differe...

erro in using GDI+ #2
I use vc6.0,something wrong when i use GDI+ : in StdAfx.h I add the following code betwen #endif//AFX_NO_AFXCMN_SUPPORT and //{{AFX+_INSERT_LOCATION}} code as following: #include "Gdiplus.h" using namespace Gdiplus; #pragma comment (Lib,"GdiPlus.LIb") but when I compile,message shows : Compling......... StdAfx.cpp gdiplusinit.h(39):erro C2065:"ULONG_PTR":undeclared identifier. .............................................................. how can i solve the problem? thanks. ...

Auto Fill?
I want to list dates in sequence by week day of Saturday. Where is the option Autofill? How about an alternative. Put your first Saturday in the topmost cell (say A1) then put =a1+7 in A2 and drag down (or fill down) "W. Wells" wrote: > > I want to list dates in sequence by week day of Saturday. Where is the > option Autofill? -- Dave Peterson Using Autofill to repeat a specific sequence requires you to identify the sequence in the first two cells. Enter the first Saturday date in the first cell. If you were using August 2006, you would enter a 5. Enter the se...

Cell Range Names
I've looked around, but can't find if it's possible for me to "Lookup" in column of cells based on group criteria and automatically add a Range Name to them. I'm busilding a business spreadsheet that has numbered day of the month in column A and the related day name in column B. A B 1 Sat 2 Sun 3 Mon I want to do comparisons with previous years same "Weeks", like "week 1", "week 2", etc. starting from the first Saturday of the year to the last Friday of the year. I need to figure out how to lookup, group and automa...

Changing the user password without the use of SA or DYNSA accounts
Is there any way to save a user password in GP v10 without the use of the SA or DYNSA login? I would like to grant security to one user to do this. Currently the save button is greyed out unless they log in as SA or DYNSA. Thank you You can do this by granting that user sysadmin rights in SQL management Studio. In SMS, expand the Security folder, then logins, then find that user, right click the user, go to properties, click server roles and check the box for sysadmin. "Junior De Alba" wrote: > Is there any way to save a user password in GP v10 without the use of the S...

use of Randbetween() in Excel
Hi to everyone. I had 10 columns in Excel. A1 B1 C1 ……J1 ..... … … ……… … … … ……… A50 B50 C50 j50 Each column uses the Randbetween() function to create integers 1-100, that is Randbetween(1,100). Is there anyway to use somehow the function so the integers in every row to be different? (I mean: A1 <> B1<> C1…….<>J1, A2 <> B2 <> C2…..<> J2,……………, A50 <> B50 <> C50 …….<> j50) Thank you. http://...

importing data using a macro #2
I'm trying to import ten sets of data in Excel. I click on import external data and then choose a file and press next. I add a space to separate the data into two columns and then press finish. I was wondering if it would be possible to use a macro to import the data. Possibly import the first one and then have the macro import the next 9 in succession. The files I import are always in succession, but always have slightly different names. For example, the data I collect today will be saved as 718cr1, 718cr2, 718cr3, etc. The last number represents the trial number and the fir...

how to split data in a range to many ranges
I have data in a range, like 1,2,3,4,5. I want to split each of them to five different ranges. How to do it. thanks. -- Paul Data|Text to columns Delimited by commas Looks like it should work. Paul wrote: > > I have data in a range, like 1,2,3,4,5. > I want to split each of them to five different ranges. > How to do it. thanks. > > -- > Paul -- Dave Peterson Thank you, Dave. I made it. "Dave Peterson" <ec35720@netscapeXSPAM.com> ???????:421882CD.22F31915@netscapeXSPAM.com... > Data|Text to columns > Delimited by commas > > Looks...

fill random cells in an area
hi! I have this problem: in one sheet a have 4 values (in the columns ABCD row1) and I want to use this 4 values to fill randomly 4 of those 20 cells of the range A1:A20. How to do this? Thank you!:confused: --- Message posted from http://www.ExcelForum.com/ i forgot something: range A1:A20 is in a new sheet not in the same shee with the values. Sorry -- Message posted from http://www.ExcelForum.com ...

Auto fill box
Hello - I am using Excel 2003 and when I drag data to fill in a series etc., there is an automatic box that pops up (auto fill) when I am finished dragging. What is this, it is always in the way and how do I get rid of it? Thank You Tools>Options>Edit. Uncheck "show paste options buttons" to get rid of the pop-up box. Gord Dibben Excel MVP On Wed, 12 Jan 2005 11:26:13 -0800, "sue t" <anonymous@discussions.microsoft.com> wrote: >Hello - I am using Excel 2003 and when I drag data to fill >in a series etc., there is an automatic box that pops up ...

delete query using inner join
The following sql in access DELETE DETAIL2.ID, DETAIL2.[DET:CUSTNO], DETAIL2.[DET:INVIDX], DETAIL2.[DET:CUSTIDX], DETAIL2.[DET:INVOICE], DETAIL2.[DET:DATE], DETAIL2.[DET:JULDATE], DETAIL2.[DET:ITEMNBR1], DETAIL2.[DET:ITEMNBR2], DETAIL2.[DET:ITEMNBR3], DETAIL2.[DET:ITEMNBR4], DETAIL2.[DET:ITEMNBR5], DETAIL2.[DET:ITEMDESC1], DETAIL2.[DET:ITEMDESC2], DETAIL2.[DET:ITEMDESC3], DETAIL2.[DET:ITEMDESC4], DETAIL2.[DET:ITEMDESC5], DETAIL2.[DET:PRICE1], DETAIL2.[DET:PRICE2], DETAIL2.[DET:PRICE3], DETAIL2.[DET:PRICE4], DETAIL2.[DET:PRICE5], DETAIL2.[DET:IPRICE1], DETAIL2.[DET:IPRICE2], D...

How do I add a formula to a range of cells
1) You can type a formula into a cell and then drag the that cell's fill handle (solid square in lower right corner) to fill other cells with the formula. You need to know about absolute and relative referencing - see Help I the cell is part of a vertical table, double clicking the fill handle is quicker than dragging it. 2) You can select a range of cell, type the formula and finish off with CTRL+ENTER. This fills the selected range with the same formula - with appropriate cell reference changes. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme ...

Drag and Fill
How can you drag and fill text in a column? For example in A1 you have "AAAA", in B1 you have "AAAB", C1 you have "AAAC" and I want to fill the rest of the column with AAAD...AAAE...and so on to ZZZZ. It does it automatically with numbers, but is it possible to do with text. Please help for Excel 2000. Marc Boken Marc, I don't believe that this is possible. "Marc Boken" <stevan.boken@wystar.com> wrote in message news:ede201c43d15$9d1f53a0$a001280a@phx.gbl... > How can you drag and fill text in a column? For example > in A...

Using a Template
I just finished writing a simple program for my job. I would like t use it as a template each time that I open the excel and save it unde a client name without messing up the template. And without saving th new file as template. Something tells me that the answer is easy bu it seems to escape me. Thanks for any help -- Jua ----------------------------------------------------------------------- Juan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=688 View this thread: http://www.excelforum.com/showthread.php?threadid=26353 Hi Juan save your workbook as a t...

how to i control autofill
Hello, Suppose I enter a formula in cell B2 and I want this formula to autofill down to cell B55000. If I click and drag down to cell B55000, there are two problems: it takes too long to scroll all the way down to cell B55000, and by the time i actually get there the scroll speed is so fast that i usually pass the cell. Is there a way to autofill a formula so that I don't have to do it by manually dragging the formula down to cell B55000? Thanks, Joe -- Message posted via http://www.officekb.com Hi two options that i know of: 1) if there is data in column A down to A55000 (or co...

How do I use copy web site
I use the visual studio 2005 development server so the File system is used. My web site is located in F:/MyTest I have added a virtual directory called MT that points to the physical directory F:/MyTest So I can start the web site from the brower when I enter http://localhost/MT/Startpage.aspx This works perfect. If I for example want to use the copy web site to copy my web site to a production directory so to speak. Can somebody tell me how I do this. I have read the documentation but does not understand what they mean. For example should I use the File system or local IIS ...

Pivot Autofilering Items
Dear All, I'd like to see only the valid items on the drop down list on the "Page Field" area . An example. Source Data: Model - Color - Full Optional - Qty FIAT BLU YES 10 OPEL GREEN NO 12 BMW RED NO 14 In the Page field I put the Model, Color and Full Optional field. When I filter the item FIAT on the field Model and then I want to filter the Color field, I'd like see only the color regarding the FIAT model (BLU), bu...

Microsoft Software used in Alternative Fuel Source
To whom this may concern, I have developed a way to used an "alternative fuel source" that will create jobs nation wide and with a bit of development, can reach out globally. In order for me to disclose more information about the subject I must receive interest and feedback from Microsoft. I have sent similar messages to Spain's Modragon as well as other leading corporations here in the U.S. The first to act will receive my full devotion to the company. If this message does not apply to this department please forward to the correct cite please. Sincerely, Anto...

Macro to select all and name range
I have a workbook with some macros that work on another workbook ("Data.xls"). One thing I want the macro to do, is to select everything in a worksheet named Survey and name the range "Database". This would be equivalent to go to the Data workbook and Survey worksheet, hitting Ctrl+Shift+End and enter Database in the name box. I can't record this action because the code specifies the cells included in the current version of Data.xls, and the number of rows will change. What code can I use to accomplish this? Richard Richard, Try this With Workbooks("D...

Outlook 2007, replies using various accounts.
I have several accounts set up in one identity. Most of the time this works well. They are A, B, and C; A being the default. Sometimes, if I am using account C for a line of correspondence, a reply will be sent from account A or B (but not always). How can I keep Outlook from changing accounts like this? I sometimes get caught out and only realise it has happened after a reply has been sent. The topic has been raised before, but I did not come across any satisfactory solution that worked. TIA. Cheers, Guy ** Stress - the condition brought about by having to ** resist the temptation t...

Autofill on addressing
Trying to set up Outlook on new PC as I had it on old one where e mail address is automatically offered when part of a name is inserted, but no luck in finding it. Trevor Appleton <trevor@weather.4dinternet.co.uk> wrote: > Trying to set up Outlook on new PC as I had it on old one where e mail > address is automatically offered when part of a name is inserted, but > no luck in finding it. What version of Outlook? For OL 2003, it's Tools>Options>E-mail Options>Advanced E-mail Options: Suggest names while completing To, Cc, and Bcc fields. -- Brian Tillman ...