Help using countif formula with VBA

[peter_z]

Hey Guys,

I was just trying to put a formula into a cell:

Formula:
=COUNTIF(STATEMENT!M:M,"cancelled")

VBA Code:

Sub test()
Dim strsource As String
Dim c As String

c = '=COUNTIF(STATEMENT!M:M,"cancelled")'

strsource = "Reference"
Application.Worksheets(strsource).Range("N1").FormulaLocal = c
End Sub

I can get it going if I change it to:
c = "=COUNTIF(STATEMENT!M:M,cancelled)"
But then the formula doesn't work.

Any ideas guys?

Cheers

 

[circledchicken]

Hi,

I think you need an extra " around cancelled, i.e.

c = "=COUNTIF(STATEMENT!M:M,""cancelled"")"

 

[peter_z]

woo hooo that works perfect.

Cheers circledchicken!

Much appreciated

 

[circledchicken]

Wooo hoo! - your welcome!