Macro to calculate hours per work order

J

Jay

I’m clear on what I want to do, but I don’t know how to proceed.
I need a macro that will:

1. Read WorkOrderNo from a list of work orders starting in cell B6 in
Dest_Wksht
2. Find all instances of WorkOrderNo in Source_Wksht that fall on Date1
(Monday), and populate cell C6 in Dest_Wksht with the total hours worked for
that Work Order number on that day.
3. Repeat for Date2 (Tuesday) thru Date7 (Sunday)
4. Repeat with cel B7 from Dest_Wksht thru until end of list

Where do I begin?
Thanks for any help.

Source_Wksht looks like this:
A B C D
2 Work Order Time In Time Out Time elapsed
3 1221 10/21/2008 10:03 10/21/2008 12:16 2:13
4 1221 10/21/2008 12:36 10/21/2008 15:58 3:21
5
6 1223 10/20/2008 14:21 10/20/2008 16:27 2:06
7 1223 10/21/2008 8:00 10/21/2008 10:03 2:03
 
R

RyanH

This code is untested, but I think it will get you started. I am assuming
you want all of Mondays Hours summed in Destination Wks Col. C, Tuesday Col.
D, and so on. If you get any errors let me know and I will try to help.
If this helps you, please let me know by clicking "YES" below.

Option Explicit

Sub SumHours()

Dim lngLastWorkOrder As Long
Dim rngWorkOrders As Range
Dim lngLastRow As Long
Dim rngSource As Range
Dim cell As Range
Dim rngFoundOrder As Range

With Sheets("Dest_Wksht")
' find last row in workorder col
lngLastWorkOrder = .Cells(Rows.Count, "B").End(xlUp).Row
If lngLastWorkOrder < 6 Then
MsgBox "There are no workorders to process.", vbInformation
Exit Sub
End If

' set range of work orders
Set rngWorkOrders = .Range("B6:B" & lngLastWorkOrder)
End With

With Sheets("Source_Wksht")
' find last row in source wks
lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
If lngLastWorkOrder < 2 Then
MsgBox "There are no workorders to process.", vbInformation
Exit Sub
End If

'set range to scan
Set rngSource = .Range("A2:A" & lngLastRow)
End With

' find workorders in source wks
For Each cell In rngWorkOrders
Set rngFoundOrder = rngSource.Find _
(What:=cell.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole)

' if workorder if found add the hours to the destination wks
If Not rngFoundOrder Is Nothing Then
Select Case Format(rngFoundOrder.Offset(0, 1).Value, "dddd")

Case "Monday"
With Sheets("Dest_Wksht").Cells(cell.Row, "C")
.Value = .Value +
Sheets("Source_Wksht").Cells(rngFoundOrder.Row, "D").Value
End With
Case "Tuesday"
With Sheets("Dest_Wksht").Cells(cell.Row, "D")
.Value = .Value +
Sheets("Source_Wksht").Cells(rngFoundOrder.Row, "D").Value
End With
Case "Wednesday"
With Sheets("Dest_Wksht").Cells(cell.Row, "E")
.Value = .Value +
Sheets("Source_Wksht").Cells(rngFoundOrder.Row, "D").Value
End With
Case "Thursday"
With Sheets("Dest_Wksht").Cells(cell.Row, "F")
.Value = .Value +
Sheets("Source_Wksht").Cells(rngFoundOrder.Row, "D").Value
End With
Case "Friday"
With Sheets("Dest_Wksht").Cells(cell.Row, "G")
.Value = .Value +
Sheets("Source_Wksht").Cells(rngFoundOrder.Row, "D").Value
End With
Case "Saturday"
With Sheets("Dest_Wksht").Cells(cell.Row, "H")
.Value = .Value +
Sheets("Source_Wksht").Cells(rngFoundOrder.Row, "D").Value
End With
Case "Sunday"
With Sheets("Dest_Wksht").Cells(cell.Row, "I")
.Value = .Value +
Sheets("Source_Wksht").Cells(rngFoundOrder.Row, "D").Value
End With

End Select
End If
Next cell

End Sub
 
J

Jay

RyanH,
Yes this did help me get started. Thank you very much.
Still having some issues -
mostly in not understanding "With" and "Case" etc.
The worksheets are actually in different workbooks, so I've modified the
code to try to alternate between the two workbooks - not sure I did it right.
Also, I'm not sure what to do with the
..Value = .Value +
I get a "Compile Error - Expected: expression"

To simplify things for my simple mind I've only taken it to Tuesday at this
point until I can test it and understand how it's working.

I appreciate your feedback!

Here's my code:

Option Explicit

Sub SumHours()

Dim lngLastWorkOrder As Long
Dim rngWorkOrders As Range
Dim lngLastRow As Long
Dim rngSource As Range
Dim cell As Range
Dim rngFoundOrder As Range
Dim SourceWkbk As Workbook

Set SourceWkbk = Workbooks.Open("H:\FAC\Dave
Sipes\DavProjTimeTracking.xls", UpdateLinks:=False, ReadOnly:=True)

With ActiveSheet
' find last row in workorder col
lngLastWorkOrder = .Cells(Rows.Count, "B").End(xlUp).Row
If lngLastWorkOrder < 6 Then
MsgBox "There are no workorders to process.", vbInformation
Exit Sub
End If

' set range of work orders
Set rngWorkOrders = .Range("B6:B" & lngLastWorkOrder)
End With

SourceWkbk.Activate
With ActiveSheet
' find last row in source wks
lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
If lngLastRow < 3 Then
MsgBox "There are no workorders to process.", vbInformation
Exit Sub
End If

'set range to scan
Set rngSource = .Range("A2:A" & lngLastRow)
End With

' find workorders in source wks
For Each cell In rngWorkOrders
Set rngFoundOrder = rngSource.Find _
(What:=cell.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole)

' if workorder if found add the hours to the destination wks
If Not rngFoundOrder Is Nothing Then
Select Case Format(rngFoundOrder.Offset(0, 1).Value, "dddd")

Case "Mon"
With
Workbooks("DraftingWeeklyActivityReport.xls").Worksheets("Sheet1").Cells(cell.Row, "D")
.Value = .Value +
SourceWkbk.Worksheets("Time Check Log").Cells(rngFoundOrder.Row, "D").Value
End With
Case "Tue"
With
Workbooks("DraftingWeeklyActivityReport.xls").Worksheets("Sheet1").Cells(cell.Row, "E")
.Value = .Value +
SourceWkbk.Worksheets("Time Check Log").Cells(rngFoundOrder.Row, "D").Value
End With


End Select
End If
Next cell

End Sub


DestWksht (in Dest Workbook) looks like this:

A B C D E
F G
4 27-Oct 28-Oct
29-Oct Total
5 Project Work Order Status Mon Tue Wed
6 1234
7 3323
8 4433
9 2334
10 4568
11
12 Total
 
R

RyanH

I see you really are new to VBA. I will do my best to explain.

1.) With is used when you do not want to type the same reference on each
line. For example, in this case Sheets("Sheet1") is used as my reference and
both examples are equivalent.

Sheets("Sheet1").Name = "Test1"
Sheets("Sheet1").Range("A1").Value = "String"

or, I could write it using the With Statement

With Sheets("Sheet1")
.Name = "Test1"
.Range("A1").Value = "String"
End With

2.) The Select...Case Statement is like a fancy If...Then Statement. Both
examples below are equivalent.

If x = 1 Then
' do something
End If
If x = 2 Then
' do that
End If
If x = 3 Then
' do this
End If

or

Select Case x
Case 1
' do something
Case 2
' do that
Case 3
' do this
End Select

3.) Try this new code I gave you. You should not have to change anything
but the constants below. Change the strSourceWbkName, strSourceWksName, etc
as I have indicated below for you.

4.) If this code produces an error indicate to me which line caused the
code and what the error says.

If this helps! Click "YES" below.

Option Explicit

Sub SumHours()

Const strSourceWbkName As String = "Source Workbook Name Here"
Const strSourceWksName As String = "Source Worksheet Name Here"
Const strDestinationWbkName As String = "Destination Workbook Name Here"
Const strDestinationWksName As String = "Destination Worksheet Name Here"

Dim wksDestination As Worksheet
Dim wbkSource As Workbook
Dim wksSource As Worksheet
Dim lngLastWorkOrder As Long
Dim rngWorkOrders As Range
Dim lngLastRow As Long
Dim rngSource As Range
Dim cell As Range
Dim rngFoundOrder As Range

Set wksDestination =
Workbooks(strDestinationWbkName).Sheets(strDestinationWksName)
Set wbkSource = Workbooks.Open("H:\FAC\DaveSipes\" & strSourceWbkName,
UpdateLinks:=False, ReadOnly:=True)
Set wksSource = Workbooks(strSourceWbkName).Sheets(strSourceWksName)

With wksDestination
' find last row in workorder col
lngLastWorkOrder = .Cells(Rows.Count, "B").End(xlUp).Row
If lngLastWorkOrder < 6 Then
MsgBox "There are no workorders to process.", vbInformation
Exit Sub
End If

' set range of work orders
Set rngWorkOrders = .Range("B6:B" & lngLastWorkOrder)
End With

With wksSource
' find last row in source wks
lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
If lngLastWorkOrder < 2 Then
MsgBox "There are no workorders to process.", vbInformation
Exit Sub
End If

'set range to scan
Set rngSource = .Range("A2:A" & lngLastRow)
End With

' find workorders in source wks
For Each cell In rngWorkOrders
Set rngFoundOrder = rngSource.Find _
(What:=cell.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole)

' if workorder if found add the hours to the destination wks
If Not rngFoundOrder Is Nothing Then
Select Case Format(rngFoundOrder.Offset(0, 1).Value, "dddd")

Case "Monday"
With wksDestination.Cells(cell.Row, "C")
.Value = .Value + wksSource.Cells(rngFoundOrder.Row,
"D").Value
End With
Case "Tuesday"
With wksDestination.Cells(cell.Row, "D")
.Value = .Value + wksSource.Cells(rngFoundOrder.Row,
"D").Value
End With
Case "Wednesday"
With wksDestination.Cells(cell.Row, "E")
.Value = .Value + wksSource.Cells(rngFoundOrder.Row,
"D").Value
End With
Case "Thursday"
With wksDestination.Cells(cell.Row, "F")
.Value = .Value + wksSource.Cells(rngFoundOrder.Row,
"D").Value
End With
Case "Friday"
With wksDestination.Cells(cell.Row, "G")
.Value = .Value + wksSource.Cells(rngFoundOrder.Row,
"D").Value
End With
Case "Saturday"
With wksDestination.Cells(cell.Row, "H")
.Value = .Value + wksSource.Cells(rngFoundOrder.Row,
"D").Value
End With
Case "Sunday"
With wksDestination.Cells(cell.Row, "I")
.Value = .Value + wksSource.Cells(rngFoundOrder.Row,
"D").Value
End With

End Select
End If
Next cell

End Sub
 
J

Jay

Thank you for the clear explanation of "With" and "Select...Case".

The code you gave me runs, and doesn't error anywhere, but it does not give
me the result I'm looking for.

It populates one cell (H7, under Fri) in the destination worksheet with a
single date and time value (10/28/2008 12:24:58 PM).
I'm not sure how it's getting this...

I did test and it is setting rngWorkOrders and rngSource correctly.

The Source worksheet "Time elapsed" columb is actually "E", not "D" as I
indicated earlier. I changed the instances of "D" in the Select...Case With
statements to "E" and now it populates the H7 cell with 1/0/1900 2:11:52 AM.
 
R

RyanH

You are right, I tested the code and it doesn't work. Switching back and
forth between worksheets makes a big difference in code. My old code would
fail horribly because I do not have references to the worksheets through out
the code. I have added those references and tested the code and everything
should work now.

Option Explicit

Sub SumHours()

Const strSourceWbkName As String = "Book2"
Const strSourceWksName As String = "Sheet2"
Const strDestinationWbkName As String = "Book1"
Const strDestinationWksName As String = "Sheet1"

Dim wksDestination As Worksheet
Dim wbkSource As Workbook
Dim wksSource As Worksheet
Dim lngLastWorkOrder As Long
Dim rngWorkOrders As Range
Dim lngLastRow As Long
Dim rngSource As Range
Dim cell As Range
Dim rngFoundOrder As Range
Dim i As Long

Set wksDestination =
Workbooks(strDestinationWbkName).Sheets(strDestinationWksName)
Set wbkSource = Workbooks.Open("H:\FAC\DaveSipes\" & strSourceWbkName,
UpdateLinks:=False, ReadOnly:=True)
Set wksSource = Workbooks(strSourceWbkName).Sheets(strSourceWksName)

With wksDestination
' find last row in workorder col
lngLastWorkOrder = .Cells(Rows.Count, "B").End(xlUp).Row
If lngLastWorkOrder < 6 Then
MsgBox "There are no workorders to process.", vbInformation
Exit Sub
End If

' set range of work orders
Set rngWorkOrders = .Range("B6:B" & lngLastWorkOrder)
End With

With wksSource
' find last row in source wks
lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row

'set range to scan
Set rngSource = .Range("A1:A" & lngLastRow)
End With

' find workorders in source wks
For Each cell In rngWorkOrders
Set rngFoundOrder = rngSource.Find _
(What:=cell.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole)

' if workorder if found in Source add the hours to the destination wks
If Not rngFoundOrder Is Nothing Then

' scan all numbers after found order number in source
' to sum all other matches
For i = rngFoundOrder.Row To lngLastRow

If cell.Value = wksSource.Cells(i, "A").Value Then
Select Case Format(wksSource.Cells(i, 2).Value, "dddd")

Case "Monday"
With wksDestination.Cells(cell.Row, "C")
.Value = .Value + wksSource.Cells(i,
"E").Value
End With
Case "Tuesday"
With wksDestination.Cells(cell.Row, "D")
.Value = .Value + wksSource.Cells(i,
"E").Value
End With
Case "Wednesday"
With wksDestination.Cells(cell.Row, "E")
.Value = .Value + wksSource.Cells(i,
"E").Value
End With
Case "Thursday"
With wksDestination.Cells(cell.Row, "F")
.Value = .Value + wksSource.Cells(i,
"E").Value
End With
Case "Friday"
With wksDestination.Cells(cell.Row, "G")
.Value = .Value + wksSource.Cells(i,
"E").Value
End With
Case "Saturday"
With wksDestination.Cells(cell.Row, "H")
.Value = .Value + wksSource.Cells(i,
"E").Value
End With
Case "Sunday"
With wksDestination.Cells(cell.Row, "I")
.Value = .Value + wksSource.Cells(i,
"E").Value
End With
End Select
End If
Next i
End If
Next cell

End Sub
 
J

Jay

I'm still getting the same results.

It looks like the Format line is not finding "Monday" correctly.

To help me narrow things down to see what the code is doing I changed the If
statement from this...

If Not rngFoundOrder Is Nothing Then

' scan all numbers after found order number in source
' to sum all other matches
For i = rngFoundOrder.Row To lngLastRow

If cell.Value = wksSource.Cells(i, "A").Value Then
Select Case Format(wksSource.Cells(i, 2).Value, "dddd")

Case "Monday"
With wksDestination.Cells(cell.Row, "C")
.Value = .Value + wksSource.Cells(i,"E").Value
End With
Case "Tuesday"
With wksDestination.Cells(cell.Row, "D")
.Value = .Value + wksSource.Cells(i,"E").Value
End With
Case "Wednesday"
With wksDestination.Cells(cell.Row, "E")
.Value = .Value + wksSource.Cells(i,"E").Value
End With
Case "Thursday"
With wksDestination.Cells(cell.Row, "F")
.Value = .Value + wksSource.Cells(i,"E").Value
End With
Case "Friday"
With wksDestination.Cells(cell.Row, "G")
.Value = .Value + wksSource.Cells(i,"E").Value
End With
Case "Saturday"
With wksDestination.Cells(cell.Row, "H")
.Value = .Value + wksSource.Cells(i,"E").Value
End With
Case "Sunday"
With wksDestination.Cells(cell.Row, "I")
.Value = .Value + wksSource.Cells(i,"E").Value
End With
End Select
End If

To this...

If Not rngFoundOrder Is Nothing Then

' scan all numbers after found order number in source
' to sum all other matches
For i = rngFoundOrder.Row To lngLastRow

If cell.Value = wksSource.Cells(i, "A").Value Then
If Format(wksSource.Cells(i, 2).Value, "dddd") =
"Monday" Then
MsgBox "Found Monday"
End If

End If
Next i

End If

The code runs withou any errors, but it does not list "Found Monday"
 
R

RyanH

I setup my worksheet like you said and the code works beautifully! There are
a few things that could keep you message from showing.

1.) Your Destination work order number must be found in the Source, thus
rngFoundOrder will equal something and not Nothing.

2.) Format(wksSource.Cells(i, 2).Value, "dddd") may not be a Monday.

If you want you can e-mail me a copy of the worksheet and I can get it
working for you.

(e-mail address removed)
 
R

RyanH

I sent you your workbooks. Your cells where not formatted correctly and that
is why you were getting weird numbers. Plus I made some small adjustments to
the code so that data would be placed in the correct place.

Please, remember to click "YES" below if this helped you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top