Macro sums values in some cells but not in others (or only partially)

[dl7631]

Hello!
I have Microsoft Home and Student 2013.
I have a macro that loops through all worksheets that contain letter "P":

[FONT=&quot]

Sub MonthSumsH()​

' Calculate the sum total in column H across all sheets that have letter P in their name​

​

Dim ws As Worksheet​

Dim LR As Long​

Dim MySum As Long​

Dim MyCell As Range​

Dim MyMonth As String​

​

Application.ScreenUpdating = False​

​

MyMonth = Format(Range("H4").Value, "mmyyyy")​

For Each ws In Worksheets​

If InStr(1, ws.Name, "P") > 0 Then​

LR = ws.Cells(Rows.Count, "G").End(xlUp).Row​

MySum = MySum + Evaluate("=SUMPRODUCT(--(TEXT(<wbr>'" & ws.Name & "'!G3:G" _​

& LR & ",""mmyyyy"")=""" & MyMonth & """),'" & ws.Name & "'!H3:H" & LR & ")")​

End If​

Next ws​

Range("I4").Value = MySum​

End Sub​

[/FONT]
[FONT=&quot]
However, recently I started noticing it doensn't sum up everything. This is really weird.
For example: in one of the most recent sheets I have an entry in H2 - and it's counted by the macro.
But after I add some amount in H3 - it's not counted by the macro! the format for both H2 and H3 is identical - 'currency'.
Even more weird - in my recent sheet the amount in H2 is counted by the macro. But when I add 100 in H3, the macro adds only 75 (!). And if I add 50 in H3, then the macro adds only 25!
How is it even possible!?
When I try to add some amount on older sheets, the whole amount is taken into account.

Any thoughts on what might be wrong? Maybe, somehow, I have too many sheets? But in this case - why is the macro taking into account the whole value in H2 but only part of the value in H3 (or not at all)?
I am at the end of my wits.
Thank you very much![/FONT]

 

[njimack]

Have you tried stepping through the code using F8? Or using Debug.Print to dump MySum to the Immediate window after every sheet?

 

[dl7631]

No, I haven't. But I did write a formula (in a couple of sheets that give me problems) and the actual sum of all cells in H is what it should be. In other words, it's not the issue of wrong cell format.
Do you know if I start with F8 - does it start with the very first sheet on the left? Or on the right? (because I have a lot of sheets)

 

[dl7631]

I just tried one more thing. I added in H3 just 1. And the total sum (after the macro) changed from 899 to 875 (!???). If I enter 2 instead, it goes from 899 to 876. So weird!

 

[njimack]

I think it cycles through the sheets in the order that they were created (I could be wrong). You can get round this though by setting a break (Select 'Next ws' and press F9). You can then press F5 and the code will run as normal but will stop every time it gets to that line.

 

[dl7631]

So, where do you recommend I set a break in my code - in what line?

 

[dl7631]

I had to re-write it - there is no way to understand why it stopped working only for some weird cells.