VBA Sum If works on active sheet but errors with other sheet

Question

What I'm trying to do is do a sumif where I take an id from sheet2 (the active sheet), look it up on sheet1 and sum data from from sheet1. I then print the result in a cell in sheet2 (the activecell.offset = ).

ActiveCell.Offset(m, n) = WorksheetFunction.SumIf(Sheets("Sheet1").Range("B:B"), Sheets("Sheet2").Cells(m, 1), Sheets("Sheet1").Range(Cells(1, covcol), Cells(5000, covcol)))

I believe the problem is trying to reference a cell on a different worksheet. Is there a direct solution or are my choices (1) use a loop with if as a sumif proxy or (2) insert sumif excel formulas into each cell? Since there's already a nested loop, I'd rather not introduce further complexity.

Thanks!

score 0 · Answer 1 · answered Sep 04 '20 at 02:57

Try something like this.

Sub standard()
    'DECLARE VARIABLE
        Dim x_matrix, y_matrix, z_matrix As Range
        Dim x_copyrange, y_copyrange, z_copyrange, time_string As String
        Dim x_step, y_step, z_step, x_fnl_row, y_fnl_row, z_fnl_row As Integer
        
    'Nested Loop
        'MATRIX
            x_fnl_row = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
            Let x_copyrange = "a" & 1 & ":" & "J" & x_fnl_row
            Set x_matrix = Worksheets("Sheet1").Range(x_copyrange)
            
            y_fnl_row = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
            Let y_copyrange = "a" & 1 & ":" & "J" & y_fnl_row
            Set y_matrix = Worksheets("Sheet2").Range(y_copyrange)

        'LOOP
            x_step = 2
            Do While x_step <= x_fnl_row
            y_step = 2
                Do While y_step <= y_fnl_row
                    'This is Match ID on one sheet to another
                    If x_matrix(x_step, 1) = y_matrix(y_step, 1) Then
                        'Do your calculation
                        
                        'Add to the Cell you want
                        
                    End
                y_step = y_step + 1
                Loop
            x_step = x_step + 1
            Loop
            
        
End Sub

VBA Sum If works on active sheet but errors with other sheet

1 Answers1