Vba, sumifs

[Gojira]

Hi, I've just registered because I've been beating my head against a vba challenge for many hours now and I'm going around in circles. I've searched extensively but I'm way out of my comfort zone in terms of VBA knowledge and am basically way over my head due to my lack of understanding.

I am attempting to take a set of timesheet data, entered by day (columns) per project & per resource (rows) and summarise it by week. It's important that I can do this in an automated way without using a pivot table as I need to present the data in a specific way that pivot tables aren't giving me.

I've got a macro that pulls all the different timesheets into a single 'master' sheet, and then a second macro that filters that master sheet and pulls out any rows that meet specific criteria (The Project name). This gives me a small (2-3 row) set of data, that runs daily from, say September to the end of December. I want to take that daily data and summarise it by week for further use.

There are 3 sheets - the 'master' Timesheet Data tab, the Filtered Data tab that contains only rows that match a specific project, and the Weekly Data tab which will hold the final result.


From what I can tell, I should be able to use a SUMIFS function in VBA to do this - and if I experiment doing it with formulae it does work. My code as it is atm is below. I apologise if it's somewhat unreadable at the bottom.. you can see I've been floundering... On the plus side I try to comment the code to remind myself what each bit does, so it may explain my thinking in places. I've added in some additional comments to explain where i've been struggling.

Sub Sum_Days()
 
' Use the SUMIFS function to add cell values between two dates. e.g. =SUMIFS(L6:AE6,L5:AE5,">="&<wbr>L1,L5:AE5,"<"&M1)
    Dim TimeTotal As Long
    Dim x As Range
    Dim CriteriaRange As Range
    Dim SumRange As Range
    Dim First_Date As Date
    Dim Last_Date As Date
    Dim y As Range
    Dim FirstSearchDate As Range
    Dim NextSearchDate As Range
   
'First lets find out the First and Last Dates of the entire range of working data
    Worksheets("Filtered Data").Activate
    Last_Date = Application.WorksheetFunction.<wbr>Max(Rows(4))
    First_Date = Application.WorksheetFunction.<wbr>Min(Rows(4))
   
    'MsgBox (" First = " & First_Date)
    'MsgBox (" Last = " & Last_Date)
   
'Now we set a Range variable to store the first date
With Worksheets("Filtered Data").Range("4:4")
    Set x = .Find(First_Date, LookIn:=xlValues)
   ' MsgBox ("X = " & x)
End With
 
'Which we then use as the starting point to select the whole range of dates and store it as our Criteria Range for a SumIF
    x.Select
    Range(ActiveCell, ActiveCell.End(xlToRight)).<wbr>Select
    Set CriteriaRange = Selection
   
'And offset that range by one row to give us the first row of 'Sum Range' data for the SumIF
   
    Set SumRange = CriteriaRange.Offset(1, 0)
    SumRange.Select

'here I'm trying to manually set the SUMIFS function up using specific data rather than variables, but even that's not working
   
TimeTotal = Application.WorksheetFunction.<wbr>SumIfs(Range("Rows(5)"), Range("Rows(4)"), "=" & CLng(M4))

'Anything below this point I've commented out because I've hit the point of despair..but this is what I really want to get working.

   ' Worksheets("Weekly Data").Activate
'   Set FirstSearchDate = Range(Application.<wbr>WorksheetFunction.Min(Rows(1))<wbr>)
  '  Set NextSearchDate = FirstSearchDate.Offset(0, 1).Value
        
 '   x.Select
        
  '  FirstSearchDate = Application.WorksheetFunction.<wbr>Min(Rows(1))
   
'With Worksheets("Weekly Data").Range("1:1")
'    Set y = .Find(First_Date, LookIn:=xlValues)
'End With
    
' MsgBox (Criteria1)
' Set Criteria2 = y.Offset(0, 1).Value
    
 'TimeTotal = Application.WorksheetFunction.<wbr>SumIfs(Arg1:=Range("SumRange")<wbr>, Arg2:=("CriteriaRange"), Arg3:=">=" & CLng(Criteria1), Arg4:=("CriteriaRange"), Arg5:="<" & CLng(Criteria2))

I think that I've set my Criteria Range and Sum Range correctly but I'm not 100% sure. I'm struggling to set the criteria variables properly, and, well, I'm desperate for some help!

and I haven't even started to think about looping the SUMIFS through more than one row yet.

 

[RoryA]

Welcome to the forum.

I confess I only skimmed the code, but your basic syntax isn't quite right - you're using VBA variables as if they were the names of ranges in the main Excel window. The syntax should be like this:

TimeTotal = Application.WorksheetFunction.SumIfs(SumRange,CriteriaRange,">=" & CLng(Criteria1),CriteriaRange,"<" & CLng(Criteria2))

 

[Gojira]

Thanks RoryA.

That makes sense, and it's progress in the right direction.

If I set only one criteria, and use a specific cell reference then it seems to work, so this:

TimeTotal = Application.WorksheetFunction.SumIfs(SumRange,CriteriaRange,">=" & CLng(L4))

Produces a sum of all of the values in the row, which is 39

However, if I change L4 to another cell in the row, e.g. P4 it still gives the result of 39, which it shouldn't be doing, it should be 35. So my confusion continues.

Further, if I then add in the second criteria, still using specific cell reference it starts reporting 0 as the result instead of what it should be (which is 6):

TimeTotal = Application.WorksheetFunction.SumIfs(SumRange, CriteriaRange, ">=" & CLng(L4)), CriteriaRange, "<" & CLng(S4))

A google later and it seems to suggest that there's some problem with the value of the criteria but they're all dates with no leading or trailing spaces.

I truly appreciate the help. I'm at my wits end.

 

[RoryA]

You can't just use L4, S4 etc to refer to a range. You need to use Range("L4") for example, so:

TimeTotal = Application.WorksheetFunction.SumIfs(SumRange,CriteriaRange,">=" & CLng(Range("L4").Value))

 

[Gojira]

You can't just use L4, S4 etc to refer to a range. You need to use Range("L4")

Thank you so much Rory, you've been an enormous help and sanity saver.

The basic Sumif is now working using variables (Criteria1 and Criteria2) that are set using data from the 'Weekly Data' tab

I can't thank you enough!

Now to figure out loops!!

 

[RoryA]

Glad to help.