Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-11-2011, 12:54 PM
carlleese24 carlleese24 is offline VBA code to change Windows XP VBA code to change Office 2003
Novice
VBA code to change
 
Join Date: Aug 2011
Posts: 2
carlleese24 is on a distinguished road
Default 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
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
Attached Files
File Type: xls Date query.xls (27.5 KB, 8 views)
Reply With Quote
  #2  
Old 08-13-2011, 08:51 AM
Catalin.B Catalin.B is offline VBA code to change Windows Vista VBA code to change Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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)
Attached Files
File Type: xls Copie a Date query.xls (39.5 KB, 9 views)
Reply With Quote
  #3  
Old 08-13-2011, 09:48 AM
carlleese24 carlleese24 is offline VBA code to change Windows XP VBA code to change Office 2003
Novice
VBA code to change
 
Join Date: Aug 2011
Posts: 2
carlleese24 is on a distinguished road
Default

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.
Reply With Quote
Reply

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
VBA code to change 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:00 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft