Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to Autofill one cell to many rows below where row count will change
Sub AutoFillDateLookups()
Dim DataRange As Range Dim CopyRange As Range Dim FillRange As Range Set DataRange = Range("ClassCountRevenue") Set CopyRange = Range("F2") Set FillRange = CopyRange.Offset(1, 0).Resize(DataRange.Rows.Count, 0).Select CopyRange.AutoFill Destination:=Range(FillRange) End Sub I am attempting to AutoFill a range of values from one cell (F2) to a range of cells below where the number of rows will vary from month to month. In the example above, I have set the datarange to capture the total rows/columns in the data table. I set the copy range to the starting cell containing my formula. I can't seem to get the fill range to be one row down and then resize to the total count of rows in the data table. Any help would be much appreciated. Thanks! Kimberly |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to Autofill one cell to many rows below where row count w
Kimberley
I approach this slightly differently. First find you last used row and then insert the required formula into all cells in the column base on this last row. This means you do not need to use the Autofill. e.g. Dim endRow As Long endRow = Range("E" & Rows.Count).End(xlUp).Row Range(Cells(2, 6), Cells(endRow, 6)).FormulaR1C1 _ = "=RC[-2]+RC[-3]" Hope this helps Rowan "TrainingGoddess" wrote: Sub AutoFillDateLookups() Dim DataRange As Range Dim CopyRange As Range Dim FillRange As Range Set DataRange = Range("ClassCountRevenue") Set CopyRange = Range("F2") Set FillRange = CopyRange.Offset(1, 0).Resize(DataRange.Rows.Count, 0).Select CopyRange.AutoFill Destination:=Range(FillRange) End Sub I am attempting to AutoFill a range of values from one cell (F2) to a range of cells below where the number of rows will vary from month to month. In the example above, I have set the datarange to capture the total rows/columns in the data table. I set the copy range to the starting cell containing my formula. I can't seem to get the fill range to be one row down and then resize to the total count of rows in the data table. Any help would be much appreciated. Thanks! Kimberly |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to Autofill one cell to many rows below where row count w
Otherwise if the formula is not coming from your VBA code to start with you
can use yuor macro as follows: Sub AutoFillDateLookups() Dim DataRange As Range Dim CopyRange As Range Dim FillRange As Range Set DataRange = Range("ClassCountRevenue") Set CopyRange = Range("F2") CopyRange.AutoFill Destination:=Range(Cells(2, 6), _ Cells(Range("E" & Rows.Count).End(xlUp).Row, 6)) End Sub Regards Rowan "TrainingGoddess" wrote: Sub AutoFillDateLookups() Dim DataRange As Range Dim CopyRange As Range Dim FillRange As Range Set DataRange = Range("ClassCountRevenue") Set CopyRange = Range("F2") Set FillRange = CopyRange.Offset(1, 0).Resize(DataRange.Rows.Count, 0).Select CopyRange.AutoFill Destination:=Range(FillRange) End Sub I am attempting to AutoFill a range of values from one cell (F2) to a range of cells below where the number of rows will vary from month to month. In the example above, I have set the datarange to capture the total rows/columns in the data table. I set the copy range to the starting cell containing my formula. I can't seem to get the fill range to be one row down and then resize to the total count of rows in the data table. Any help would be much appreciated. Thanks! Kimberly |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to Autofill one cell to many rows below where row count will change
Kimberly,
Several things ... Resize: you must omit or specify the number of columns, 0 is not valid. AutoFill: the destination range must include the source range. FillRange: It is already a range object, so Range(FillRange) is invalid. The following will work if DataRange is a valid range object... '---------------------------------------- Sub AutoFillDateLookups() Dim DataRange As Range Dim CopyRange As Range Dim FillRange As Range Set DataRange = Range("ClassCountRevenue") Set CopyRange = Range("F2") Set FillRange = CopyRange.Resize(DataRange.Rows.Count + 1, 1) CopyRange.AutoFill Destination:=FillRange End Sub '------------------------------------------- Regards, Jim Cone San Francisco, USA "TrainingGoddess" wrote in message oups.com... Sub AutoFillDateLookups() Dim DataRange As Range Dim CopyRange As Range Dim FillRange As Range Set DataRange = Range("ClassCountRevenue") Set CopyRange = Range("F2") Set FillRange = CopyRange.Offset(1, 0).Resize(DataRange.Rows.Count, 0).Select CopyRange.AutoFill Destination:=Range(FillRange) End Sub I am attempting to AutoFill a range of values from one cell (F2) to a range of cells below where the number of rows will vary from month to month. In the example above, I have set the datarange to capture the total rows/columns in the data table. I set the copy range to the starting cell containing my formula. I can't seem to get the fill range to be one row down and then resize to the total count of rows in the data table. Any help would be much appreciated. Thanks! Kimberly |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to Autofill one cell to many rows below where row count w
Thanks for your help! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to Autofill one cell to many rows below where row count will change
Thanks for your help! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code to count rows | Excel Worksheet Functions | |||
Autofill cell references with a pattern of every 110 rows | Excel Worksheet Functions | |||
How do I count rows within a cell? | Excel Worksheet Functions | |||
excel deleting rows last cell does not change. How to change? | Excel Discussion (Misc queries) | |||
run code after cell contents change | Excel Programming |