Error when entering formula using VBA

[Harry12345]

I have a formula that I'm using which works and gives me the right value.

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

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

 

[VoG]

Try

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

 

[Harry12345]

Try

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]

You are welcome. Essentially, you need to double up any quotes within a formula to be entered using VBA.