VBA Code s not working even though the sum of filtered values are equal to 0.

[mrahulkanth]

Set SumRange = ReconWS.Range(Cells(2, AmountFilter), Cells(ReconRows, AmountFilter)).SpecialCells(xlCellTypeVisible)
If Application.WorksheetFunction.Sum(SumRange) = 0 Then
    ReconWS.Range(Cells(2, MatchedItem),Cells(ReconRows,MatchedItem)).SpecialCells(xlCellTypeVisible).Select
    Selection.Value = "Matched to zero"

I have written this code to verify if sum of filtered values is equal to 0 or not. Even though the sum of filtered values is equal to 0, this code is not working.
I also checked the format of filtered values - I can see that in some places the sum is 0 ; 0.00 ; (0.00). But all three formats are equal to 0 right? but the code is not working. Please help!

 

[jasonb75]

Not working in what way?

Runtime error?
Does nothing?
Does something, but not what is expected?
Something else?

Cells is unqualified (no sheet name in front) in several places, so that could be a problem depending on which sheet is active.

Regarding formatting, anything greater than (0.005) and less than 0.005 will appear as 0 with a format of 0.00 but will still have a non zero value for the purpose of your sum function. Number formats only affect the appearance of the numbers, the original value still remains in the background.

 

[mrahulkanth]

Hi @jasonb75 . Actually the code does nothing even though the sum of filtered values are equalling to 0. I don’t why it’s happening like that. For some filters it’s working fine when the sum is equal to 0. But in some filters it’s not working(does nothing) and skips to the next step. Please help!

 

[Fluff]

If you add this msgbox as shown

MsgBox Application.WorksheetFunction.Sum(SumRange)
If Application.WorksheetFunction.Sum(SumRange) = 0 Then

what does it say when you expect it to be zero?

 

[mrahulkanth]

Hi @Fluff When I use the Msgbox like you said. It displays only the first visible cell value, not the sum.

 

[Fluff]

Does it make any difference if you use

Set SumRange = ReconWS.Range(ReconWS.Cells(2, AmountFilter), ReconWS.Cells(ReconRows, AmountFilter)).SpecialCells(xlCellTypeVisible)
MsgBox Application.WorksheetFunction.Sum(SumRange)

 

[mrahulkanth]

No @Fluff . Actually it doesn’t prompt any error, but despite being the sum of filtered values equal to 0, it update the cells as “Not Matched”. That’s my problem. I just want that if the sum of filtered(visible) cells equals to 0, then it should update as “Matched to Zero”.

 

[Fluff]

What does the message box say?

 

[mrahulkanth]

Msgbox gives only the value which is the first visible cell in the filtered range.

 

[Fluff]

And what was that value?
What EXACTLY did the message box say?