How to use variables in a VBA code using the sumif function

Question

I download a data set that always has a different number of rows. I store two columns as variables, the imports and the months. Then I need to run a Sumif formula that sums the value of imports by the months. I am writing a Sumif formula that uses the two variables and references the cell to its left.

The cells however vary in location based on the changing size of the data set. So I write a code to select the last active cell on a column and select the cell 3 rows down.

When writing the formula with the variables and the cell its giving me an error. Please help sorry for any typos fist time doing this.

I select all the active cells in range D and store them as months, I do the same for the imports. Then using range I find the last active cell on column M, and use select the cell 3 rows down, where I wish to write my formula.

Please see my codes to see what am I doing wrong, I am a novice coder.

Sub Importaciones()
'
' Importaciones Macro
'




    Dim LastRow As Long

        LastRow = Range("L" & Rows.Count).End(xlUp).Row 

    Dim Months As Long

        Months = Range("D2", Range("D2").End(xlDown)).Select

    Dim Imports As Long

        Imports = Range("M2", Range("M2").End(xlDown)).Select

     Dim LastRowM As Long

      LastRowM = Range("M" & Rows.Count).End(xlUp).Row

      Range("M" & LastRowM + 3).Formula = "=sumif(" & Months & ", " & 
      Range("L" & LastRow + 3) & ", " & Imports & ")"

End Sub

For the formula to work and the sum of the month that I choose comes up

This is definitely a duplicate of at least one other question that I can't find at the moment, but the answer is exactly what you're already doing with "M" & Rows.Count. i.e. something like "=sumif(" & Months & ", " & Range("L" & LastRow + 3) & ", " & Imports & ")" — Mathieu Guindon
If its a dupllicate can you show me the link for the question I am allegedley duplicating? — Myrnaloy
The link is at the top of the post; nothing wrong with asking a duplicate question, it happens all the time - note that I've also provided you with a more personalized solution. — Mathieu Guindon
Thanks, however after reading the post you linked and you copying the solution you offered its still not working. Any other tips Range("M" & LastRowM + 3).Formula = "=sumif(" & Months & ", " & Range("L" & LastRow + 3) & ", " & Imports & ")" — Myrnaloy
You will want to declare Months and Imports as Ranges and set them without the .Select on the end. You will then want to use Months.Address(0,0) and Imports.Address(0,0) in the formula @MathieuGuindon provided. — Scott Craner

Scott Craner Scott Craner · Accepted Answer · 2019-09-05T21:27:30

As per all the comments:

Sub Importaciones()

    With Worksheets("Sheet1") 'Change to your sheet
        Dim LastRow As Long
            LastRow = .Range("L" & .Rows.Count).End(xlUp).Row

        Dim Months As Range
            Set Months = .Range("D2", .Range("D2").End(xlDown))

        Dim Imports As Range
            Set Imports = .Range("M2", .Range("M2").End(xlDown))

         Dim LastRowM As Long
            LastRowM = .Range("M" & .Rows.Count).End(xlUp).Row

            .Range("M" & LastRowM + 3).Formula = "=sumif(" & Months.Address(0, 0) & ", " & .Range("L" & LastRow + 3).Address(0, 0) & ", " & Imports.Address(0, 0) & ")"
    End With
End Sub

How to use variables in a VBA code using the sumif function

1 Answers