Run Time Error 1004 SumIf function

Question

I'm trying to use the Sumif function in VBA, however, I'm getting a run-time error. The error started when I tried to add the variable CritLastCol into the formula. If I replace that with an actual cell reference it works. I want to be able to autofill the rows below this and my criteria will change based on the row. In other words my sum range and criteria range are fixed and my criteria is the cell to the left of my activecell (but not one over, it could be 20, 30 columns over).

Sub SumBydimcode()
Dim lastCol As Integer
Dim CritLastCol As String
lastCol = Cells(3 & Columns.Count).End(xlToLeft).Column
lastRow = Range("A" & Rows.Count).End(xlUp).Row
NewLastRow = Range("I" & Rows.Count).End(xlUp).Row
CritLastCol = ActiveCell.End(xlToLeft).Value

   ActiveCell.FormulaR1C1 = "=SUMIF(R3C8:R" & lastRow & "C8," & CritLastCol & ",R3C10:R" & lastRow & "C" & lastCol & ")"

    ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":J" & NewLastRow)

End Sub

Answer 1

You need to surround your CritLastCol variable in quotation marks. To do so in VBA, you use the double-quote twice "". As a result, your code would look like this:

ActiveCell.FormulaR1C1 = "=SUMIF(R3C8:R" & lastRow & "C8,""" & CritLastCol & """,R3C10:R" & lastRow & "C" & lastCol & ")"

Notice the extra double-quotes surrounding CritLastCol

Answer 2

Ok, I figured out a way to do it... Instead of autofill, I used a loop starting at the end of my data and working back up.

Sub SumBydimcode()
Dim lastCol As Integer
Dim CritLastCol As String
lastCol = Cells(3 & Columns.Count).End(xlToLeft).Column
lastRow = Range("A" & Rows.Count).End(xlUp).Row
NewLastRow = Range("I" & Rows.Count).End(xlUp).Row
NewFirstRow = lastRow + 7


S = ActiveCell.Row
If S > NewLastRow - NewFirstRow Then
For S = ActiveCell.Row To NewFirstRow Step -1

    CritLastCol = ActiveCell.End(xlToLeft).Value
   ActiveCell.FormulaR1C1 = "=SUMIF(R3C8:R" & lastRow & "C8,""" & CritLastCol & """,R3C10:R" & lastRow & "C" & lastCol & ")"

ActiveCell.Offset(-1, 0).Select

Next S
End If          

End Sub