Macros

  • Thread starter Thread starter Mary
  • Start date Start date
M

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
 
one way:

With Range("A2")
.FormulaR1C1 = "=VLOOKUP(R[-1]C[6]," & _
"'D:\seexcel3\[pocomplete.xls]Sheet1'!R2C1:R996C9,6,0)"
.AutoFill Destination:=.Resize(999, 1), Type:=xlFillDefault
End With
 
I copied in your formula, which I am going to use for 4-5
columns. However, when I did this it said I would reset
macros or something. I accepted, but guess I shouldnt
have. Now ctrl p is the printer, which I think is the
default. That is no problem, but no matter what key I
use with ctrl, it won't work. However, the one macro
that I had done before still works. I have changed teh
macro back to what I had. It still doesn't work unless I
unhide the personal.xls that has the macros. Then I
choose run macro and it will run then. I even tried to
record it over from scratch. It seems now I can only get
the 1 macro to work. I am sure it must be a setting or
something?

Mary
-----Original Message-----
one way:

With Range("A2")
.FormulaR1C1 = "=VLOOKUP(R[-1]C[6]," & _
"'D:\seexcel3\[pocomplete.xls]Sheet1'! R2C1:R996C9,6,0)"
.AutoFill Destination:=.Resize(999, 1), Type:=xlFillDefault
End With


Mary said:
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.
Click to expand...
.
Click to expand...
 
Back
Top