VBA syntax error

[Coryj5]

I am trying to make some corrections to a series of identicle tabs in a sheet, so i created this macro.

Sub corrections()

With Range ("M15")
.Value = "=COUNTIF(A15:A21,"YES")"
End with

End Sub
.... along with some other corrections, but the root lies in this one action.

I receive a syntax error being caused by the quotes around YES, but that is required for the formula to work properly.
Am i doing something silly that is right in front of me here?...

Thanks,

coryj5

 

[Jonmo1]

When entering a formula in a cell via VBA, and that formula contains quotes...
You have to double up the quotes.

Also, try using the .Formula property, isntead of .Value

With Range ("M15")
    .Formula = "=COUNTIF(A15:A21,""YES"")"
End with

 

[Domenic]

Try...

With Range ("M15")
     .Formula = "=COUNTIF(A15:A21,""YES"")"
End with

 

[Rick Rothstein]

Text constants are surrounded by quote marks... quote marks within them must be doubled-up (the method VB uses to see a quote mark as a quote mark character without any special meaning) so VB can tell those internal quotes are not marking the beginning or end of a text constant (if they did, they are missing the ampersand concatenation symbols which is why you are getting the syntax error). Try your line like this...

.Value = "=COUNTIF(A15:A21,""YES"")"

 

[Coryj5]

Worked perfect! thanks all.

I did try and use the .formula for an if statement and that did not work. i had to use .value.