Formula to VBA

R

Ronbo

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.
 
J

Jim Jackson

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.
 
B

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("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)
 
R

Ronbo

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.
 
R

Ronbo

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.
 
B

Bob Phillips

No, unprotect/protect is the way.

--
HTH

Bob Phillips

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top