Question about formulas and ranges.

[Juan Correa]

Hello,

I have the following piece of code (taken from having recorded the
particular steps into a macro).

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/28/2009 by Juan S. Correa
'

'
Range("AD2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-18],REGION,2,FALSE)"
Range("AD2").Select
Selection.AutoFill Destination:=Range("AD2:AD4513")

End Sub

The code will add the VLOOKUP formula in cell AD2 of my spreadsheet and then
will copy/paste that formula down all the way down to AD4513. That would be
OK if the amount of data comming into the spreadsheet were constant, but some
it varies every month so defining the range as ("AD2:AD4513") is not
practical in my particular case.

What I'm looking for is a way for Excel to copy/paste the formula all the
way down to the last populated row regardless of how many rows of data there
are on the spreadsheet.

Thanks in advance,
Juan Correa

 

[Mike H]

Try this,

Note we aren't now selecting

Range("AD2").FormulaR1C1 = "=VLOOKUP(RC[-18],REGION,2,FALSE)"
Lastrow = ActiveSheet.UsedRange.Rows.Count
Range("AD2").AutoFill Destination:=Range("AD2:AD" & Lastrow)

Mike

 

[FSt1]

hi
first find the last row..
Dim r As Long
r = Cells(Rows.Count, "D").End(xlUp).Row "use any column needed
then plug that into the fill range
Range("D2").AutoFill Destination:=Range("AD2:AD" & r)

the whole thing might look like this...
Dim r As Long
r = Cells(Rows.Count, "D").End(xlUp).Row
Range("AD2").FormulaR1C1 = "=VLOOKUP(RC[-18],REGION,2,FALSE)"
Range("AD2").AutoFill Destination:=Range("AD2:AD" & r)


regards
FSt1

 

[Juan Correa]

Thank you very much Mike. That worked like a charm.

JC

Try this,

Note we aren't now selecting

Range("AD2").FormulaR1C1 = "=VLOOKUP(RC[-18],REGION,2,FALSE)"
Lastrow = ActiveSheet.UsedRange.Rows.Count
Range("AD2").AutoFill Destination:=Range("AD2:AD" & Lastrow)

Mike

Hello,

I have the following piece of code (taken from having recorded the
particular steps into a macro).

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/28/2009 by Juan S. Correa
'

'
Range("AD2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-18],REGION,2,FALSE)"
Range("AD2").Select
Selection.AutoFill Destination:=Range("AD2:AD4513")

End Sub

The code will add the VLOOKUP formula in cell AD2 of my spreadsheet and then
will copy/paste that formula down all the way down to AD4513. That would be
OK if the amount of data comming into the spreadsheet were constant, but some
it varies every month so defining the range as ("AD2:AD4513") is not
practical in my particular case.

What I'm looking for is a way for Excel to copy/paste the formula all the
way down to the last populated row regardless of how many rows of data there
are on the spreadsheet.

Thanks in advance,
Juan Correa