VBA sumif with variable range

Question

I'm new to VBA, so I have to ask you for a help. Below, I got VBA code which should sum data from different sheet [AB] based on value from first column of active sheet. The number of rows and columns is variable, so cell adresses need to be absolute.

Sub sumif_test

Range("A1").Select
Selection.End(xlDown).Select
abc = ActiveCell.Row
Selection.End(xlUp).Select
Selection.End(xlToRight).Select

ActiveCell.Offset(1, 1).Select

Dim mycrit As Variant
Dim myval As Variant
Dim CritRng As Range
Dim SumRng As Range

Set CritRng = Worksheets("AB").Range("A:A")
Set SumRng = Worksheets("AB").Range("N:N")

c = ActiveCell.Column
r = ActiveCell.Row

For r = 2 To abc - 2
mycrit = Cells(r, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)
myval = Cells(r, c).Address(RowAbsolute:=False, ColumnAbsolute:=False)
myval = Application.WorksheetFunction.SumIf(CritRng, mycrit, SumRng)

Next r

End sub

With this code I don't get any result.

You are using a range address as myCrit, should you be using cells(r,1).value? — Nathan_Sav, May 04 '16 at 07:37
Unfortunately it doesn't help. Can you think of other cell address format which could work? — Borro, May 04 '16 at 07:48

score 0 · Answer 1 · edited May 23 '17 at 10:33

You need ranges and you need to set them. In your code, if you write debug.print after myval like this:

For r = 2 To abc - 2
   mycrit = Cells(r, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)
   myval = Cells(r, c).Address(RowAbsolute:=False, ColumnAbsolute:=False)
   myval = Application.WorksheetFunction.SumIf(CritRng, mycrit, SumRng)
   debug.print myval
Next r

End sub

you would probably get some result in the immediate window. Ctrl+G. In order to get some result try with:

For r = 2 To abc - 2
    mycrit = Cells(r, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    myval = Cells(r, c).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    myval = Application.WorksheetFunction.SumIf(CritRng, mycrit, SumRng)
    Cells(r,c) = myval
Next r

End sub

Finally - 2 things - try to avoid usage of select and format your code :) Here you may find some examples of sumif with VBA.

score 0 · Accepted Answer · answered May 04 '16 at 08:10

Try to avoid using select statements. Also learn to use the Application.ScreenUpdating property to speed up your code (This disables the screen refreshing between each write to the excel sheet and updates everything at the end making it much faster)

Sub sumif_test()
    Dim ws As Worksheet
    Dim NoCol As Integer, NoRow As Integer
    Dim CritRng As Range, SumRng As Range

    Application.ScreenUpdating = False

    Set ws = Worksheets("AB")

    With ws
        NoRow = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row
        NoCol = .Cells(1, .Cells.Columns.Count).End(xlToLeft).Column
        Set CritRng = .Range("A:A")
        Set SumRng = .Range("N:N")
    End With

    For r = 2 To NoRow
        Cells(r, NoCol) = WorksheetFunction.SumIf(CritRng, Cells(r, 1), SumRng)
    Next r

    Application.ScreenUpdating = True
End Sub

I've rewritten your code to avoid using the select statement. Please take a look at it and see if you can figure out what each part does.

VBA sumif with variable range

2 Answers2