Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill using a macro
Im 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 cant 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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill using a macro
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" wrote in message ... | Im 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 cant 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill using a macro
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 "katamy" wrote in message ... Im 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 cant 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill using a macro
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: Im 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 cant 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill using a macro
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: Im 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 cant 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill using a macro
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: Im 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 cant 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill Macro. | Excel Programming | |||
Autofill macro help!! | Excel Programming | |||
Autofill Macro | Excel Programming | |||
Autofill Macro | Excel Programming | |||
Autofill macro | Excel Discussion (Misc queries) |