asked on

vba runtime 1004 Application-defined or object-defined error while trying to insert a formula!

Hi ,

I get vba runtime 1004 Application-defined or object-defined error while trying to insert a formula!

.Range("BG2:BG" & Lastrow).Formula = "=IF(OR(ISNUMBER(AC2)=FALSE;AC2=0);'N/A';AX2/AC2)" ' This line gives the error but works if I put directly into cell!
.Range("BG2:BG" & Lastrow).NumberFormat = "0.00" ' this line works fine!

Thanks

Pete

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

I'm not sure if VBA can resolve LastRow so try this.

Dim lngLastRow As Long
lngLastRow = Activesheet.Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

ActiveSheet.Range("BG2:BG" & lngLastRow).Formula = "=IF(OR(ISNUMBER(AC2)=FALSE;AC2=0);'N/A';AX2/AC2)"

Open in new window

Peter Groves

ASKER

Hi Norie , yes I tried that already, with same result!

Martin , I use lastrow for 6 other formula inserts and they all work fine! But your code now gives type mismatch on your line lnglast......!

Pete

Martin Liss

Martin , I use lastrow for 6 other formula inserts and they all work fine!

Are the inserts done via VBA?

But your code now gives type mismatch on your line lnglast......!

That's pretty standard code and it works without error for me as long as there is data on the sheet. In your existing code is there data on the sheet when you are entering the formula via VBA?

Norie

Pete

What exactly did you try based on my code?

Did you change anything?

Peter Groves

ASKER

Norie, it works now, but I had to not only add the double quotes which I tried , but had to remove the also
replace the semicolons with commas!

.Range("BG2:BG" & Lastrow).Formula = "=IF(OR(ISNUMBER(AC2)=FALSE,AC2=0),""N/A"",AX2/AC2)"
Once the formula is uploaded you see semicolons where I put the commas!

Pete

Martin Liss

When I manually enter =IF(OR(ISNUMBER(AC2)=FALSE;AC2=0);'N/A';AX2/AC2 into cell BG2, Excel tells me there's an error in the formula. I'm not sure what the problem is, but I'm pretty sure that if you correct the formula, the VBA will work.

Peter Groves

ASKER

I just noticed in the second example that you changed N/A to NA() , but it was the combination on commas and double quotes that did it!

Thanks

Pete

Martin Liss

Darn, one minute too late.

Norie

Pete

A little confused, in the code I posted I though I had replaced the semi-colons with commas.

Anyway, as long as you've got it working.:)

Peter Groves

ASKER

Thanks anyways Martin! Your code would have probably worked but I already had it working for 6 other formula insertions! , this way
its uniform!

Pete

Peter Groves

ASKER

Yes you did, but I missed the change from 'N/A' to NA() so you second formula would have worked also! However
I used the double quote method elsewhere and rather keep it uniform there as well! Its the semicolons to commas that got me.

Thanks again
Pete