Formula to VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a formula;

=IF(ISNA(VLOOKUP($A13&H$10,'PC
INPUT'!$A:$G,7,FALSE)),"0",(VLOOKUP($A13&H$10,'PC INPUT'!$A:$G,7,FALSE)))

I need to put the formula into VBA but I get a application/object define
error with the following code;

Range("H13").Select
ActiveCell.Formula = "=IF(ISNA(VLOOKUP($A13&H$10,'PC
INPUT'!$A:$G,7,FALSE)),0,(VLOOKUP($A13&H$10,'PC INPUT'!$A:$G,7,FALSE))"
Selection.AutoFill Destination:=Range("H13:P13"), Type:=xlFillDefault
Range("H13:P13").Select
Selection.AutoFill Destination:=Range("H13:P48"), Type:=xlFillDefault
Range("H13:P48").Select

Any advise on how to correct the error would be appreciated.
 
Just looking at your code, I believe the 0 should look like this: ""0""
Anything with quotes in a formula needs double quotes when used in this way.
 
It worked for me, but it can be simplified

Const sLookup As String = _
"VLOOKUP($A13&H$10,'PC INPUT'!$A:$G,7,FALSE)"
Range("H13:P48").Formula = "=IF(ISNA(" & sLookup & "),0," & sLookup &
")"
Range("H13:P48").Select


Is the sheet protected?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Thanks, thats what it needed.

Jim Jackson said:
Just looking at your code, I believe the 0 should look like this: ""0""
Anything with quotes in a formula needs double quotes when used in this way.
Click to expand...
 
Bob - Thanks for the response. It is very likly that this sheet will be
protected and I found that neither code will work. I could unprotect/protect
in the code but if there are other ways, I would be interested.
 
No, unprotect/protect is the way.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Back
Top