VBA code to change

carlleese24 · #1 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Code to add dates	terricritch	Excel Programming	10	11-08-2011 09:51 AM
Word07-Bid Tab Code	ddw23	Word VBA	0	07-28-2011 01:07 PM
Fastest way to run a code	b0x4it	Word VBA	4	05-18-2011 07:54 PM
"Change Word Options" don't change	ADKREV	Word	0	11-12-2010 10:56 AM
FileSaveAs with Code	bsmith	Office	0	01-05-2006 08:43 AM