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]

Have you checked the value of the ReconRows variable?

 

[mrahulkanth]

I have 3 filtered cells based on criteria
1. 170.56
2. (85.28)
3. (85.28)

msgbox is showing the first visible cell which is 170.56

 

[Fluff]

Which explains it's not working, the msgbox value is the value to are comparing to zero.
Jason has given a very possible reason.

 

[mrahulkanth]

ReconRows covers all the rows in the sheet and from that we are considering only filtered right?.
Filters are working perfectly but the only problem here is that sum is coming to 0, but the code is treating it as not matched to zero. I see a small difference is when I observe the sums of filtered cells in status bar... it displays in different ways like 0.00 & (0.00)

 

[mrahulkanth]

B

Which explains it's not working, the msgbox value is the value to are comparing to zero.
Jason has given a very possible reason.

But for some filters it’s working and it’s ignoring for only some filters.

 

[Fluff]

only problem here is that sum is coming to 0

Wrong, it is not summing the cells. What is the value of ReconRows, when you set the sumrange?

 

[mrahulkanth]

Wrong, it is not summing the cells. What is the value of ReconRows, when you set the sumrange?

ReconRows is something like Lastrow. So from 2nd row till last row I’m considering and from that also, we are taking special visible cells. Please correct me if I’m wrong.

 

[Fluff]

What is the actual value of it?
The reason I ask is that if the message box only gave you the value of the first filtered cell, that if the only cell in the sumrange.

 

[jasonb75]

But what is the value of ReconRows? If it is not defined correctly then that could be the source of the problem.

Try adding the line

MsgBox SumRange.Address

What does that tell you?

To eliminate any rounding errors, I would suggest changing your code to

If Round(Application.WorksheetFunction.Sum(SumRange), 2) = 0 Then

but I don't think that will fix the entire problem.

 

[Fluff]

Cross posted VBA Code s not working even though the sum of filtered values are equal to 0. - OzGrid Free Excel/VBA Help Forum

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.