3

I have some problem with sumifs in vba:

Dim Arg1 As Range 'the range i want to sum
Dim Arg2 As Range 'criteria range
Dim Arg3 As Variant 'the criteria

Set Arg1 = ThisWB.Sheets("Sheet1").Range("B2:B100")
Set Arg2 = ThisWB.Sheets("Sheet1").Range("C1:C100")
Set Arg3 = ThisWB.Sheets("Sheet2").Range("A2:A12")

For i = 2 To 12
Workbooks("x.xlsx").Worksheets("Sheet2").Cells(i, LastColumn) _
= Application.WorksheetFunction.SumIfs(Arg1, Arg2, Arg3)


Next

I always get a "Type mismatch" error

Could anybody help me to fix the code?

Thank you in advance.

Community
  • 1
  • 1
CsCs
  • 43
  • 1
  • 1
  • 7

1 Answers1

3

https://msdn.microsoft.com/en-us/library/office/ff193011.aspx

Sub test()
    Dim Arg1 As Range 'the range i want to sum
    Dim Arg2 As Range 'criteria range
    Dim Arg3 As Variant 'the criteria

    'Arg1 and Arg2 must be the same size
    Set Arg1 = Sheets("Sheet1").Range("B2:B100")
    Set Arg2 = Sheets("Sheet1").Range("C2:C100")

    'this is the criteria
    Arg3 = "=False"

    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim i As Integer
    For i = 2 To 12
        ws.Cells(i, 8).Value = Application.WorksheetFunction.SumIfs(Arg1, Arg2, Arg3)
    Next
End Sub

You can also specify Arg3 as a variant and pass a single-cell range if it has the criteria. Criteria can be True/False (=False), a number (20) or a string (">100"). 

    Dim Arg3 As Variant 'the criteria
    Arg3 = Sheets("Sheet2").Range("A2")

EDIT: I realized what you were trying to do. Each cell in Arg3 is a separate criteria that you want to do SumIf on. Here is the revised code.

Sub test2()
    Dim ThisWB As Workbook: Set ThisWB = ThisWorkbook
    Dim i As Integer
    Dim LastColumn As Integer: LastColumn = 3

    Dim Arg1 As Range 'the range i want to sum
    Dim Arg2 As Range 'criteria range
    Dim Arg3 As Range 'the criteria (range)

    Set Arg1 = ThisWB.Sheets("Sheet1").Range("B2:B100")
    Set Arg2 = ThisWB.Sheets("Sheet1").Range("C2:C100")
    Set Arg3 = ThisWB.Sheets("Sheet2").Range("A2:A12")

    For i = 2 To 12
        Workbooks("x.xlsx").Worksheets("Sheet2").Cells(i, LastColumn) _
            = Application.WorksheetFunction.SumIfs(Arg1, Arg2, Arg3.Cells(i - 1, 1).Value)
    Next
End Sub

Note how Arg3 is used in SumIfs Arg3.Cells(i - 1, 1).Value. Also note that Arg1 and Arg2 must be the same size.

D_Bester
  • 5,723
  • 5
  • 35
  • 77
  • I tried the solvation but it is not working: Subscript out of range. Do you have any idea? Thank you in advance – CsCs Nov 25 '16 at 06:25
  • You need to figure out which line and which item is out of range. Do you actually have a workbook named "x.xlsx" already open? Do you have a "Sheet2"? etc. The code worked fine for me so something is different. If you can't figure it out ask another question. Your SumIf problem (Mismatch error) is already solved. Now you're asking about out-of-range error; that's a separate problem and needs to be a separate question. – D_Bester Nov 25 '16 at 11:33
  • Yeah, you were right. I made a mistake when try the code.. Now I try it again with more attention and it is working.. So thank you very much! – CsCs Nov 25 '16 at 13:39