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!

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
"katamy" <(E-Mail Removed)> wrote in message
news:6B34006A-27E0-4525-9CE4-(E-Mail Removed)...
| 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!

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"katamy" <(E-Mail Removed)> wrote in message
news:6B34006A-27E0-4525-9CE4-(E-Mail Removed)...
> 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!

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)


"katamy" wrote:

> 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!

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)"



--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"katamy" wrote:

> 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!

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

"katamy" wrote:

> 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!