Sumif in VBA will not accept equation (accepts as string)

Question

I am attempting to utilize a SumIf function to iterate down a table of names, and to sum all "Performance Savings" attributed to each name in a separate data table.

My code is below. When I run the macro, it crashes on the SumIf line. When I remove the '=' character before the SumIf function to assign the cellvalue as a string rather than formula, the macro runs fine, and the string is exactly what I would like it to be. I'm not sure why it crashes when I then make the SumIf line a formula.

I have attempted to reference analogous historic threads without success. Appreciate any help I can receive!

Dim tempStr As String

For y = 1 To (b - 3)
    ActiveSheet.Cells((a + 1 + y), 1).Select
    tempStr = ActiveCell.Value
    Sheets("Pivot").Cells((a + 1 + y), 5).FormulaR1C1 = _
        "=SUMIF('Performance Savings'!$B$2:$B$200," & Chr(34) & tempStr & Chr(34) & ",'Performance Savings'!$C$2:$C$200) "

Next y

Please look at the answers in the following thread - I hope they'll be useful: http://stackoverflow.com/questions/341258/write-a-formula-in-an-excel-cell-using-vba — , Dec 19 '14 at 19:14

score 0 · Answer 1 · answered Dec 19 '14 at 19:05

0

You need to place values in a and b

answered Dec 19 '14 at 19:05

Gary's Student

95,722
10
59
99

score 0 · Answer 2 · edited May 23 '17 at 12:05

Gene's answer was not very clear but he was alluding to the fact that you should be using .Formula syntax instead of R1C1. Also, as a general rule of thumb you should Never use the Select method.

Dim tempStr As String

For y = 1 To (b - 3)
    tempStr = ActiveSheet.Cells((a + 1 + y), 1).Value
    Sheets("Pivot").Cells((a + 1 + y), 5).Formula = _
        "=SUMIF('Performance Savings'!$B$2:$B$200," & Chr(34) & tempStr & Chr(34) & ",'Performance Savings'!$C$2:$C$200) "

Next y

Sumif in VBA will not accept equation (accepts as string)

2 Answers2