Sumif Function in vba and how to use dynamic range in formula

Question

I tried sumif function through excel vba but i'm getting '#value" error.

Sub xd()
R_lastrow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
R_lastcolumn = Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

o_lastrow = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
o_lastcolumn = Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Column

For k = 2 To o_lastrow
    For j = 2 To o_lastcolumn
Sheets("Sheet2").Cells(k, j).Value = Sheets("Sheet2").Evaluate("SUMIF(Sheet1!&range(cells(2,R_lastrow),cells(2,R_lastcolumn)),range(1,j),Sheet1!&range(cells(j,R_lastrow))")
    Next j
Next k
End Sub

Could you please suggest me where i'm wrong

I suggest you first describe what you are trying to do. I think this line Sheets("Sheet2").Cells(k, j).Value = Sheets("Sheet2").Evaluate("SUMIF(Sheet1!&range(cells(2,R_lastrow),cells(2,R_lastcolumn)),range(1,j),Sheet1!&range(cells(j,R_lastrow))") should be: Sheets("Sheet2").Cells(k, j).Value = Sheets("Sheet2").Evaluate("SUMIF(Sheet1!" & range(cells(2,R_lastrow) & "," & cells(2,R_lastcolumn)) & "," & range(1,j),Sheet1!&range(cells(j,R_lastrow))) — jkpieterse
– jkpieterse, Commented Jun 9, 2016 at 5:27
In my workbook i have list of items in column 'a' and from column 'b' to column 'd' year wise sales totals. And In sheet2 i have only unique items list in column 'a' and column 'b' to column 'd' years (Header). With help of sumif function sum the values through using vba code. — sai krishna
– sai krishna, Commented Jun 9, 2016 at 5:32
@jkpieterse - thank you, but i'm getting error. could you please check once the code, i tired to resolve the error but not done by self — sai krishna
– sai krishna, Commented Jun 9, 2016 at 6:13
Compile error : Type-declaration character does not match declared data type. This error occurring in "Criteria" argument. — sai krishna
– sai krishna, Commented Jun 9, 2016 at 6:15

Tim Williams · Accepted Answer · 2016-06-09 06:18:36Z

1

You need to substitute the range addresses into the formula.

Untested:

Sub xd()

    Const f As String = "SUMIF(Sheet1!<r1>,<r2>,Sheet1!<r3>)"

    R_lastrow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
    R_lastcolumn = Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

    o_lastrow = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
    o_lastcolumn = Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Column

    f1 = Replace(f, "<r1>", Range(Cells(2, R_lastrow), _
                                  Cells(2, R_lastcolumn)).Address(False, False))

    For k = 2 To o_lastrow
        For j = 2 To o_lastcolumn
            frm = Replace(f1, "<r2>", Cells(1, j).Address(False, False))
            frm = Replace(frm, "<r3>", Cells(j, R_lastrow).Address(False, False))
            Debug.Print frm
            Sheets("Sheet2").Cells(k, j).Value = Sheets("Sheet2").Evaluate(frm)
        Next j
    Next k
End Sub

answered Jun 9, 2016 at 6:18

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sai krishna Over a year ago

It working, but it giving '0' value, the sum of sheet1 values

Tim Williams Over a year ago

You need to debug your count if formula: get it working in a cell first.

sai krishna Over a year ago

Wiliams - I run the code and it shows 'frm' value as "Sumif(sheet1!D2:H2,B1,sheet1!H2)". But, i think my data starts from B2. could you please suggest next step

sai krishna Over a year ago

Thank you, I did few corrections in code and finally i got as per my requirement.

Collectives™ on Stack Overflow

Sumif Function in vba and how to use dynamic range in formula

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related