VB Sumif

Posted by Patrick on July 28, 2000 3:47 AM

I am trying to use the worksheet function 'Sumif' in VB. I can get it to work to a point. My problem comes when I try to define the criteria using a cell reference. Initially I declare a variable for the criteria but when it is a cell reference it falls over. Sample code is as follows:

Sub test()
Dim a As Range
Dim c As Range
Set a = Worksheets("Sheet 1").Columns(1)
b$ = Worksheets("Sheet 2").Cells(11, 1) **Problem line**
Set c = Worksheets("Sheet 1").Columns(5)
cm = Application.WorksheetFunction.SumIf(a,
b$, c)
End Sub

Thanks

Patrick

Posted by Ivan Moala on July 28, 0100 4:44 AM

Patrick
The following orked for me

Sub test()
Dim a As Range
Dim c As Range
Dim b$, cm

Set a = Worksheets("Sheet1").Columns(1)
b$ = Worksheets("Sheet2").Cells(11, 1) ' **Problem line**
Set c = Worksheets("Sheet1").Columns(5)
cm = Application.WorksheetFunction.SumIf(a, b$, c)

End Sub

Ivan

Posted by Christmas Carol on July 28, 0100 4:58 AM

Patrick - I assume that Sheet2!Cells(11,1) contains the criteria for the SUMIF?

In your code on your problem line, your missing the "text" property, ie: Worksheets("Sheet 2").Cells(11, 1) .text

Hence, it may work sometimes and not othertimes.