Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
autofill formula to dynamic row count
I need help on how to fill formula into dynamic rows. I import data into
column A-K with different row count each time I import. Then I have formula in column L-W in row 2 which I need to autofill to the last row of column A-K. Row 1 is my header row. I need a macro that will help me with this task. Thanks a lot for help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
autofill formula to dynamic row count
I usually do something like this:
lRow = aWS.cells(aWS.Rows.Count,1).end(xlup).row 'Gets last row of column 1 Set myRange = aws.cells(2,1).resize(lrow-2+1,1) myRange.offset(0,10).formulaR1C1 = "=...." 'Adds the formula 10 columns to the right of myRange You may need to adjust the resize. I'm not completely certain that I resized it correctly. HTH, Barb Reinhardt "Peakie" wrote: I need help on how to fill formula into dynamic rows. I import data into column A-K with different row count each time I import. Then I have formula in column L-W in row 2 which I need to autofill to the last row of column A-K. Row 1 is my header row. I need a macro that will help me with this task. Thanks a lot for help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
autofill formula to dynamic row count
This post has more info
lRow = aWS.cells(aWS.Rows.Count,1).end(xlup).row 'Gets last row of column 1 lCol = aws.cells(1,aws.columns.count).end(xltoleft).colum n Set myRange = aws.cells(2,1).resize(lrow-2+1,1) for i = 12 to lCol set myCell = aws.cells(myrange.row,1).offset(0,i) if myCell.hasformula then myRange.offset(0,i).formulaR1C1 = _ mycell.formulaR1C1 end if next i You may need to adjust the resize. I'm not completely certain that I resized it correctly. You may also be able to use FILLDOWN, but I'd have to record something to do that. HTH, Barb Reinhardt "Peakie" wrote: I need help on how to fill formula into dynamic rows. I import data into column A-K with different row count each time I import. Then I have formula in column L-W in row 2 which I need to autofill to the last row of column A-K. Row 1 is my header row. I need a macro that will help me with this task. Thanks a lot for help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
autofill formula to dynamic row count
Thanks a lot, Barb. I will try your suggestio. "Barb Reinhardt" wrote: This post has more info lRow = aWS.cells(aWS.Rows.Count,1).end(xlup).row 'Gets last row of column 1 lCol = aws.cells(1,aws.columns.count).end(xltoleft).colum n Set myRange = aws.cells(2,1).resize(lrow-2+1,1) for i = 12 to lCol set myCell = aws.cells(myrange.row,1).offset(0,i) if myCell.hasformula then myRange.offset(0,i).formulaR1C1 = _ mycell.formulaR1C1 end if next i You may need to adjust the resize. I'm not completely certain that I resized it correctly. You may also be able to use FILLDOWN, but I'd have to record something to do that. HTH, Barb Reinhardt "Peakie" wrote: I need help on how to fill formula into dynamic rows. I import data into column A-K with different row count each time I import. Then I have formula in column L-W in row 2 which I need to autofill to the last row of column A-K. Row 1 is my header row. I need a macro that will help me with this task. Thanks a lot for help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Autofill | Excel Discussion (Misc queries) | |||
Formula to count every other column (dynamic range) | New Users to Excel | |||
Using Count() in Dynamic Formula | Excel Discussion (Misc queries) | |||
Autofill formula to last row with data when rows will be dynamic | Excel Programming | |||
autofill with dynamic range again | Excel Programming |