Can't fill XL Formula in VBA

  • Thread starter Thread starter Lift Off
  • Start date Start date
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
 
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
 
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
 
Back
Top