#1
|
|||
|
|||
VBA code to change
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 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 |
#2
|
|||
|
|||
you might not need a macro for this job, it is not a difficult task, it may be solved with the right calculation formulas; check your file modified and attached; (i added a condition column in sheet 1, for use in summary sheet formula)
|
#3
|
|||
|
|||
Thanks
But a VBA code would be ideal on the Hours sheet the list range will be variable and it could reach 65,000 rows. |
Thread Tools | |
Display Modes | |
|
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 |