autofill formula to dynamic row count

[Peakie]

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.

 

[Barb Reinhardt]

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

 

[Barb Reinhardt]

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).column

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]

Thanks a lot, Barb. I will try your suggestio.

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).column

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