SumIf function in Vba with variable columns

Question

I'm trying to do a sumif formula, but am having difficulties. First, I want to move to the cell next to the last cell on row A3. My first part seems to accomplish that. Second, I want to do a sumif formula (sumif(range,criteria, sum_range)) where the value lands on the active cell. However, I can't seem to get the formual to work. My number of columns can change so I can't have that be constant. I want the range to be from G1 through the last active column (i.e. JW1 or some other value instead of JW), my criteria to be the letter "M" and my sum range to be G3 through the last active column, which would match the last active column in my first range. Then, I want to autofill down to the last row, where my first range is locked, but the sum range isn't locked.

Sub Sumif()


Dim lastColumn As Range
Dim NextlastColumn As Range

lastColumn = Range("G1" & Column.Count).End(xlToRight).Column
NextlastColumn = Range("G3" & Column.Count).End(xlToRight).Column
Range("A3").Select
Selection.End(xlToRight).Offset(, 1).Select

ActiveCell.Formula = "=SUMIF(lastColumn,{"M"}, NextlastColumn))"


End Sub

Answer 1

I have an answer but it isn't the way I wanted it to work. I did a normal sum if function way far out to the right and then did another macro that deletes blank columns afterwards.

Sub Netpay()
' netpay Macro

lastRow = Range("A" & Rows.Count).End(xlUp).Row

    Range("KU1").Select
    ActiveCell.Value = "Net Pay"

    Sheets("Sheet1").Select
    Range("A4:D4").Select
    Selection.Copy
    Sheets("Job Cost").Select
    Range("KU3").Select
    ActiveCell.FormulaR1C1 = "=SUMIF(R1C8:R1C305,""M"",RC[-299]:RC[-2])"
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(R1C8:R1C305,""M"",RC[-299]:RC[-2])-SUMIF(R1C8:R1C305,""N"",RC[-299]:RC[-2])-SUMIF(R1C8:R1C305,""S"",RC[-299]:RC[-2])-SUMIF(R1C8:R1C305,""T"",RC[-299]:RC[-2])"
    Range("KU3").Select
    Selection.AutoFill Destination:=Range("KU3:KU" & lastRow)
End Sub

And then this

Sub DeleteBlankColumns()

Dim lColumn As Long
 Dim iCntr As Long
 lColumn = 1000
 For iCntr = lColumn To 1 Step -1
 If Cells(1, iCntr) = "" Then
 Columns(iCntr).Delete
 End If
 Next
End Sub

Answer 2

I suspect you just want something like this:

Sub Sumif()

Dim lastColumn As Long

lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(3, lastColumn + 1).FormulaR1C1 = "=SUMIF(R1C3:R1C" & lastColumn & ",""M"",RC3:RC" & lastColumn & ")"

End Sub