SumIf getting hung by formatting

[jocoop]

Howdy, I have this little snippet of code. The SumIf is summing data that is 30 minute intervals and matching to a single line. So for example, Im trying to make sure the sum of 48 intervals from one file match a single daily forecast from another file. I sum the interval date in the varible "Test" and compare it to another variable. The interval data is created automatically from eWFM. This works well on most files, but there are a couple that the system creates different formatting, and puts a formatting like 5/10/2011 2:00 that causes "Test" to come out as a zero. I have tried to add a vba.date or a vba.left to the Sumif below, but keep messing up the syntax. Any thoughts?


For dayscount = 0 To (WeekstoRun * 7) - 1
TestDay = VBA.DateAdd("d", dayscount, StartDate)

Test = Application.WorksheetFunction.SumIf(Workbooks(IDPName(d)).Sheets(1).Range(Cells(2, 3), Cells(5000, 3)), TestDay, Workbooks(IDPName(d)).Sheets(1).Range(Cells(2, counter4), Cells(5000, counter4)))

 

[Jonmo1]

Welcome to the board...

I'm not sure this is the problem, but there is 1 big flaw I see.
Frankly surprised it works at all because of it...

When you use syntax like
Range(Cells(..,..), Cells(..,..))

And you qualify the Sheet on the RANGE part.
You must also qualify the Sheet on the internal Cells parts.

Try

With Workbooks(IDPName(d)).Sheets(1)
    Test = Application.Sumif(.Range(.Cells(2, 3), .Cells(5000, 3)), TestDay, .Range(.Cells(2, counter4), .Cells(5000, counter4)))
End With

Hope that helps.