CODEWITHSUNDEEP
excelvbaexcel-formulasumifs
Myrnaloy
Myrnaloy

Reputation: 13

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

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

Upvotes: 1

Views: 766

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

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

Upvotes: 1

Related Questions