Microsoft Office Forums - View Single Post

carlleese24 · 08-11-2011, 12:54 PM

Hi

I would like to change this macro code to be modified so it will sum up the hours depending on the criterias instead of doing a lookup.

Please see below for the code

Code:

Sub Lookup()

    
    Dim wshours As Worksheet, wsSummary As Worksheet
    Dim Lastrow&, i&, j&
    Dim Hours, Summary, Hours_worked()
    
    Set wshours = Sheets("Hours")
    Set wsSummary = Sheets("Summary")
    
    Lastrow = wshours.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Hours = wshours.Range("A2:d" & Lastrow)
    
    Lastrow = wsSummary.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Summary = wsSummary.Range("A6:f" & Lastrow)
    ReDim Hours_worked(1 To UBound(Summary, 1), 1 To 1)
    
    For i = 1 To UBound(Summary, 1)
        For j = 1 To UBound(Hours, 1)
  
            If (Hours(j, 1) = Summary(i, 1) And Sheets("Summary").Range("d2") >= Hours(j, 4) And Sheets("Summary").Range("c2") <= Hours(j, 4)) Then
                Hours_worked(i, 1) = Hours(j, 3)
                Exit For
            End If
    Next j, i
    
    wsSummary.Range("b6:b" & Lastrow) = Hours_worked
    
End Sub

On the file I have 2 sheets ones is Hours with the following titles at row 1

Employee (A)
Hours (B)
£ per hours (C)
Date (D)

The other sheet is called Summary with the following titles at row 5
Name (A)
Hours worked (B)

I also have cells for dates which on cells C2 (start date) and D2 (end date) so users can type in a date and press a button so they can sum up the data in any date range

Please could anyone help me to make this possible.

Carl