Express formula in VBA

[wasntme]

Hi,

I am trying to change some formula in my file with VBA calculations, but as the formula is bit complex, have troubles expressing it in VBA. Result of the formula should be count of all projects started this month from 2 sheets different than the one it is present in. Formula in sheet works perfect.

Formula in sheet is:

COUNTIFS('VAT registrations'!M:M,">="&EOMONTH(TODAY(),-1)+1,'VAT registrations'!M:M,"<"&EOMONTH(TODAY(),0)+1,'VAT registrations'!E:E,"Registration")+COUNTIFS(Archive!L:L,">="&EOMONTH(TODAY(),-1)+1,Archive!L:L,"<"&EOMONTH(TODAY(),0)+1,Archive!E:E,"Registration")

Have tried to simply put it in VBA as:

Worksheets("Summary").Range("C18").Formula = "=COUNTIFS('VAT registrations'!M:M,">="&EOMONTH(TODAY(),-1)+1,'VAT registrations'!M:M,"<"&EOMONTH(TODAY(),0)+1,'VAT registrations'!E:E,"Registration")+COUNTIFS(Archive!L:L,">="&EOMONTH(TODAY(),-1)+1,Archive!L:L,"<"&EOMONTH(TODAY(),0)+1,Archive!E:E,"Registration")"

But i get Compile error:expected end of statement.

Any help is greatly appreciated.

 

[steve the fish]

You could always record the placing of the formula in the cell, however that will result in a RC reference. You need to use two double quotes around your values where you want excel to place double quotes in the formula:

Range("C18").Formula = "=COUNTIFS('VAT registrations'!M:M,"">=""&EOMONTH(TODAY(),-1)+1,'VAT registrations'!M:M,""<""&EOMONTH(TODAY(),0)+1,'VAT registrations'!E:E,""Registration"")+COUNTIFS(Archive!L:L,"">=""&EOMONTH(TODAY(),-1)+1,Archive!L:L,""<""&EOMONTH(TODAY(),0)+1,Archive!E:E,""Registration"")"

 

[wasntme]

Hi steve,

Thank you, i knew i had to use double quotes somewhere, now i know exactly where