ActiveCell.Formula = Variable Not Working-CodePudding

I am trying to fill the active cell with a formula. An example of the desired formula is =SUMIF(K30:K40,DescVar,E30:E40) where DescVar is string variable.

Here is the snip of code that is giving me problems:

If Selection.Value <> DescVar Then

Selection.Offset(1, -6).Select

Dim CalcRow As Integer, FromRow As Integer, ToRow As Integer
CalcRow = ActiveCell.Row
FromRow = CalcRow   1
ToRow = FromRow   10

Dim SumFormula As String
SumFormula = "='SUMIF(K" & FromRow & ":K" & ToRow & "," & DescVar & ",E" & FromRow & ":E" & ToRow & ")"

ActiveCell.Formula = SumFormula

End If

Any ideas?

CodePudding user response：

Since the end result needs to be something like =SUMIF(K1:K10,"foo",E1:E10), you need to add the quotes around DescVar, and they need to be doubled up:

SumFormula = "=SUMIF(K" & FromRow & ":K" & ToRow & ",""" & DescVar & """,E" & FromRow & ":E" & ToRow & ")"