Can't fill XL Formula in VBA

L

Lift Off

I have the following formula that works fine as long as I manually fil
down on the sheet:

=IF(ISNA(VLOOKUP(A9,$P$8:$Q$94,2,FALSE)),"",VLOOKUP(A9,$P$8:$Q$94,2,FALSE))

When I put the code into VBA it compiles but hangs up when run. Th
VBA code is:

Range("K8").Select
ActiveCell.FormulaR1C1
"=IF(ISNA(VLOOKUP(A9,$P$8:$Q$94,2,FALSE)),"",VLOOKUP(A9,$P$8:$Q$94,2,FALSE))"
Selection.AutoFill Destination:=Range("K8:K153")
Type:=xlFillDefault

I get a "run time error '1004', Application-defined or object-define
error." Hitting debug the editor jumps to my "ActiveCell line abov
suggesting the error is in the formula.

What gives? Any help is appreciated.

Lift Of
 
P

pauluk

See if this cade works?

Range("K8").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISNA(VLOOKUP(R[1]C[-10],R8C16:R94C17,2,FALSE)),"""",VLOOKUP(R[1]C[-10],R8C16:R94C17,2,FALSE))"
Range("K8").Select
Selection.AutoFill Destination:=Range("K8:K153")
Type:=xlFillDefault
Range("K8:K153").Selec
 
L

Lift Off

Paul: That did it. I had relative in there at first but changed it t
A1 in the post. I noticed you added the second range select. Why i
it necessary to do that? Just curious. Need to learn.

Thanks again, Lift Of
 
Top