CODEWITHSUNDEEP
vbaexcelsyntax
faifai
faifai

Reputation: 23

vba syntax error when writing sumif formula

I try to write this line of code in VBA as the following:

Range("S5")="=SUMIF(L2:L793,">=0")"

However, there is a syntax error when I try to run the code. The error is highlighted as ")". Hope someone can help with this problem!

Upvotes: 1

Views: 108

Answers (1)

Vityata
Vityata

Reputation: 43585

As mentioned in the comments, the problem is in the escaping of the " sign. In general, this is an easy way to "translate" any Excel formula to VBA:

  1. Write the formula in Excel, thus it works ok.
  2. Select the cell with the formula.
  3. Run this code:
Public Sub PrintMeUsefulFormula()

    Dim strFormula  As String
    Dim strParenth  As String

    strParenth = """"

    strFormula = Selection.Formula
    strFormula = Replace(strFormula, """", """""")

    strFormula = strParenth & strFormula & strParenth
    Debug.Print strFormula

End Sub
  1. Take a look at the immediate window (Ctrl+ G).

Upvotes: 1

Related Questions