AutoFill using a macro

[katamy]

I’m working in Excel 2003. I have a report which runs in another program that
I save in Excel. I then create a macro to insert columns at various places
and perform a vlookup. The data varies each time the report is run. I know
there is a code that will allow me to AutoFill down to the last row of that
column, but I can’t remember how to do it. Any suggestions?

Here is the code…

Range("X2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Look up Vince report.xls]Function'!C1:C2,2,FALSE)"
Selection.AutoFill Destination:=Range("X2:X2649")
Range("X2:X2649").Select

Thanks in advance for your help!

 

[Jim Rech]

This is one way to fill down the selection based on the column to the left:

With Selection
Range(.Offset(0, -1), .Offset(0, -1).End(xlDown)).Offset(0,
1).FillDown
End With


--
Jim
| I’m working in Excel 2003. I have a report which runs in another program
that
| I save in Excel. I then create a macro to insert columns at various places
| and perform a vlookup. The data varies each time the report is run. I know
| there is a code that will allow me to AutoFill down to the last row of
that
| column, but I can’t remember how to do it. Any suggestions?
|
| Here is the code…
|
| Range("X2").Select
| ActiveCell.FormulaR1C1 = _
| "=VLOOKUP(RC[-1],'[Look up Vince
report.xls]Function'!C1:C2,2,FALSE)"
| Selection.AutoFill Destination:=Range("X2:X2649")
| Range("X2:X2649").Select
|
| Thanks in advance for your help!

 

[Don Guillett]

Try it this way. Modify ranges to suit.

Sub makeformulaandfilldown()
lr = Cells(Rows.Count, "b").End(xlUp).Row
Range("e2:e" & lr).Formula = "=vlookup(b2,$c$2:$d$" & lr & ",2,false)"
End Sub

 

[Joel]

I used column W to determine the Last row of the worksheet. then I used a
simple copy to copy the formula from row 2 down to the last row

Range("X2").FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Look up Vincereport.xls]Function'!C1:C2,2,FALSE)"
lastRow = Range("W" & rows.count).end(xlup).Row
Range("X2").Copy _
Destination:=Range("X2:X" & LastRow)

 

[Michael]

all you need is this if the last row never changes:
Range("X2:X2649").FormulaR1C1 = "=VLOOKUP(RC[-1],'[Look up Vince
report.xls]Function'!C1:C2,2,FALSE)"

If you need to find the last row do this:
ilastrow=Range("X65536").End(xlup).row
Range("X2:X" & ilastrow).FormulaR1C1 = "=VLOOKUP(RC[-1],'[Look up Vince
report.xls]Function'!C1:C2,2,FALSE)"

 

[PetLahev]

Hi katamy

I assume that you need this

Note, for column "X"

Dim l_LastRow As Long

l_LastRow = Cells(Rows.Count, "X").End(xlUp).Row

Range("X2:X" & l_LastRow).FormulaR1C1 = "=VLOOKUP(RC[-1],'[Look up Vince
report.xls]Function'!C1:C2,2,FALSE)"
' but I prefer that
Range(Cells(2, "X"), Cells(l_LastRow, "X")).FormulaR1C1 =
"=VLOOKUP(RC[-1],'[Look up Vince report.xls]Function'!C1:C2,2,FALSE)"

Premek