Formula to VBA

[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("H1313"), Type:=xlFillDefault
Range("H1313").Select
Selection.AutoFill Destination:=Range("H1348"), Type:=xlFillDefault
Range("H1348").Select

Any advise on how to correct the error would be appreciated.

 

[Guest]

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.

 

[Bob Phillips]

It worked for me, but it can be simplified

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


Is the sheet protected?

--
HTH

Bob Phillips

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

 

[Guest]

Thanks, thats what it needed.

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.

 

[Guest]

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.

 

[Bob Phillips]

No, unprotect/protect is the way.

--
HTH

Bob Phillips

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