Macro

[Mary]

I have this macros and it works, but it pauses a few
minutes to do the fill down. Is there a way just to
entered a range. I tried using this line, but with no
fill down range.

Range("A2:A1000").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-1]C[6],'D:\seexcel3\[pocomplete.xls]
Sheet1'!R2C1:R996C9,6,0)"

But when I do that it only puts the formula in A2. Of
course, I would like all the ranges to be the same.

Macro
Sub openpo()
'
' openpo Macro
' Macro recorded Mary Klafert
'
' Keyboard Shortcut: Ctrl+p
'
Workbooks.Open Filename:="D:\seexcel3\openpomac.xls"
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-1]C[6],'D:\seexcel3\[pocomplete.xls]
Sheet1'!R2C1:R996C9,6,0)"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-1]C[5],'D:\seexcel3\[pocomplete.xls]
Sheet1'!R2C1:R996C9,6,0)"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-1]C[-2],'D:\seexcel3\[sales
order.xls]Sheet1'!R2C1:R996C8,5,0)"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-1]C[-3],'D:\seexcel3\[sales
order.xls]Sheet1'!R2C1:R996C8,2,0)"

Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-1]C[-4],'D:\seexcel3\Sample\[sales
order.xls]Sheet1'!R2C1:R996C8,4,0)"
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-4],'D:\seexcel3\[pocomplete.xls]
Sheet1'!R2C1:R996C9,5,0)"
Range("A2:E1498").Select
Selection.FillDown
Range("K2:K1498").Select
Selection.FillDown
Range("A1").Select
ActiveWorkbook.Save
End Sub

 

[Jan Karel Pieterse]

Hi Mary,

Range("A2:A1000").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-1]C[6],'D:\seexcel3\[pocomplete.xls]
Sheet1'!R2C1:R996C9,6,0)"

But when I do that it only puts the formula in A2. Of
course, I would like all the ranges to be the same.

Make that:

Range("A2:A1000").FormulaR1C1 = _
"=VLOOKUP(R[-1]C[6],'D:\seexcel3\[pocomplete.xls]
Sheet1'!R2C1:R996C9,6,0)"

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com