Error when entering formula using VBA

Harry12345

New Member
Joined
May 21, 2014
Messages
5
I have a formula that I'm using which works and gives me the right value.

Code:
=COUNTIF(bommech[PO1 No],"")+COUNTIF(bomelec[Procurement Status],G4)
However when I try and enter the exact same formula through VBA using, I get "Run-time error 1004: Application defined or object defined error". I don't see why the formula will work when I enter it directly into Excel but it won't enter the formula through VBA.

Code:
ActiveSheet.Range("H4").formula = "=COUNTIF(bommech[PO1 No],"")+COUNTIF(bomelec[Procurement Status],G4)"
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

Rich (BB code):
ActiveSheet.Range("H4").Formula = "=COUNTIF(bommech[PO1 No],"""")+COUNTIF(bomelec[Procurement Status],G4)"
 

Harry12345

New Member
Joined
May 21, 2014
Messages
5
Try

Rich (BB code):
ActiveSheet.Range("H4").Formula = "=COUNTIF(bommech[PO1 No],"""")+COUNTIF(bomelec[Procurement Status],G4)"
Yes! It works, been trying for ages to get it work, such a simple solution. Cheers mate
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
You are welcome. Essentially, you need to double up any quotes within a formula to be entered using VBA.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,113
Messages
5,466,755
Members
406,497
Latest member
Bryanlim

This Week's Hot Topics

Top