Vba sumifs

[mikeymay]

I am wanting to get a value from a table by using the SUMIFS function within a VBA routine

I am using the following code

Application.WorksheetFunction.SumIfs(rngAssFTE, rngAssPerson, strPerson, rngAssRole, strRole, rngAssDate, ">=" & dtRoleStart, rngAssDate, "<=" & dtRoleEnd)

to get what should be a value of 168 but it is returning 0.

When I recreate this in Excel using the formula - SUMIFS(tbl_Assigned[Assigned],tbl_Assigned[Name],Resource!B5,tbl_Assigned[Role],Resource!C5,tbl_Assigned[Date],">="&Resource!E5,tbl_Assigned[Date],"<="&Resource!F5)

I get the correct result.

I have checked and triple checked that the ranges in the VBA function are correct and the string and date values are also correct.

 

[Joe78man]

I think we need more input here. Do you want to use the result for a variable? do you want the script to write the value in a cell? VBA is not that intuitive, it differs what you'd do in vba from what you'd write in the cells.

 

[steve the fish]

Someone far more inteliigent than me could maybe explain but when dealing with dates i always dimension them to long.

Dim dtRoleStart As Long, dtRoleEnd As Long

See if that works.

 

[mikeymay]

Well spank my ar*e and call me susan!!!

Thanks Steve....I must have spent 2 hours trying to see what the problem is here and that has fixed it!! Excel can be a d*ck sometimes!!!!