Usage of SUMIF formula in the VBA code
 

I am trying to use Excel "SUMIF" function in VBA. I was able to do it without
problem by inserting the formula into the cell ( Range(Cells(21, 2), Cells(21
+ RowNumber, 2 + ColumnNumber)).Formula = "=sumIf($a$1:$A$16, $a21,
b$1:b$16)").

But, when I tried to do the same thing using "WorksheetFunction." (
Range(Cells(35, 2), Cells(35 + RowNumber, 2 + ColumnNumber)) =
WorksheetFunction.SumIf(Range(Cells(1, 1), Cells(16, 1)), Range(Cells(21 +
RowNumber, 1), Cells(21 + RowNumber, 1)), Range(Cells(1, 2 + ColumnNumber),
Cells(16, 2 + ColumnNumber)))
) I do not get the same results. It seems to me they are equivalent and
should provide the same results. But, the results says I am wrong and I was
wondering what I am missing here? Thanks.

Here is the entire code:

Sub test()
For RowNumber = 0 To 2
For ColumnNumber = 0 To 3
Range(Cells(21, 2), Cells(21 + RowNumber, 2 + ColumnNumber)).Formula
= "=sumIf($a$1:$A$16, $a21, b$1:b$16)"
Range(Cells(35, 2), Cells(35 + RowNumber, 2 + ColumnNumber)) =
WorksheetFunction.SumIf(Range(Cells(1, 1), Cells(16, 1)), Range(Cells(21 +
RowNumber, 1), Cells(21 + RowNumber, 1)), Range(Cells(1, 2 + ColumnNumber),
Cells(16, 2 + ColumnNumber)))
Next
Next
End Sub

Usage of SUMIF formula in the VBA code
 

Try

Cells(35 + RowNumber, 2 + ColumnNumber) = _
WorksheetFunction.SumIf(Range(Cells(1, 1), Cells(16, 1)), Cells(21 + _
RowNumber, 1), Range(Cells(1, 2 + ColumnNumber), Cells(16, 2 + ColumnNumber)))

HTH

"GreenInIowa" wrote:

I am trying to use Excel "SUMIF" function in VBA. I was able to do it without
problem by inserting the formula into the cell ( Range(Cells(21, 2), Cells(21
+ RowNumber, 2 + ColumnNumber)).Formula = "=sumIf($a$1:$A$16, $a21,
b$1:b$16)").

But, when I tried to do the same thing using "WorksheetFunction." (
Range(Cells(35, 2), Cells(35 + RowNumber, 2 + ColumnNumber)) =
WorksheetFunction.SumIf(Range(Cells(1, 1), Cells(16, 1)), Range(Cells(21 +
RowNumber, 1), Cells(21 + RowNumber, 1)), Range(Cells(1, 2 + ColumnNumber),
Cells(16, 2 + ColumnNumber)))
) I do not get the same results. It seems to me they are equivalent and
should provide the same results. But, the results says I am wrong and I was
wondering what I am missing here? Thanks.

Here is the entire code:

Sub test()
For RowNumber = 0 To 2
For ColumnNumber = 0 To 3
Range(Cells(21, 2), Cells(21 + RowNumber, 2 + ColumnNumber)).Formula
= "=sumIf($a$1:$A$16, $a21, b$1:b$16)"
Range(Cells(35, 2), Cells(35 + RowNumber, 2 + ColumnNumber)) =
WorksheetFunction.SumIf(Range(Cells(1, 1), Cells(16, 1)), Range(Cells(21 +
RowNumber, 1), Cells(21 + RowNumber, 1)), Range(Cells(1, 2 + ColumnNumber),
Cells(16, 2 + ColumnNumber)))
Next
Next
End Sub

Usage of SUMIF formula in the VBA code
 

Thanks, Toppers.

I could not believe this was this simple!

"Toppers" wrote:

Try

Cells(35 + RowNumber, 2 + ColumnNumber) = _
WorksheetFunction.SumIf(Range(Cells(1, 1), Cells(16, 1)), Cells(21 + _
RowNumber, 1), Range(Cells(1, 2 + ColumnNumber), Cells(16, 2 + ColumnNumber)))

HTH

"GreenInIowa" wrote:

I am trying to use Excel "SUMIF" function in VBA. I was able to do it without
problem by inserting the formula into the cell ( Range(Cells(21, 2), Cells(21
+ RowNumber, 2 + ColumnNumber)).Formula = "=sumIf($a$1:$A$16, $a21,
b$1:b$16)").

But, when I tried to do the same thing using "WorksheetFunction." (
Range(Cells(35, 2), Cells(35 + RowNumber, 2 + ColumnNumber)) =
WorksheetFunction.SumIf(Range(Cells(1, 1), Cells(16, 1)), Range(Cells(21 +
RowNumber, 1), Cells(21 + RowNumber, 1)), Range(Cells(1, 2 + ColumnNumber),
Cells(16, 2 + ColumnNumber)))
) I do not get the same results. It seems to me they are equivalent and
should provide the same results. But, the results says I am wrong and I was
wondering what I am missing here? Thanks.

Here is the entire code:

Sub test()
For RowNumber = 0 To 2
For ColumnNumber = 0 To 3
Range(Cells(21, 2), Cells(21 + RowNumber, 2 + ColumnNumber)).Formula
= "=sumIf($a$1:$A$16, $a21, b$1:b$16)"
Range(Cells(35, 2), Cells(35 + RowNumber, 2 + ColumnNumber)) =
WorksheetFunction.SumIf(Range(Cells(1, 1), Cells(16, 1)), Range(Cells(21 +
RowNumber, 1), Cells(21 + RowNumber, 1)), Range(Cells(1, 2 + ColumnNumber),
Cells(16, 2 + ColumnNumber)))
Next
Next
End Sub