Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to calculate hours per work order
Im clear on what I want to do, but I dont 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to calculate hours per work order
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 -- Cheers, Ryan "Jay" wrote: Im clear on what I want to do, but I dont 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to calculate hours per work order
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").Work sheets("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").Work sheets("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 "RyanH" wrote: 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 -- Cheers, Ryan "Jay" wrote: Im clear on what I want to do, but I dont 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to calculate hours per work order
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(strDestina tionWksName) Set wbkSource = Workbooks.Open("H:\FAC\DaveSipes\" & strSourceWbkName, UpdateLinks:=False, ReadOnly:=True) Set wksSource = Workbooks(strSourceWbkName).Sheets(strSourceWksNam e) 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 -- Cheers, Ryan "Jay" wrote: 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").Work sheets("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").Work sheets("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 "RyanH" wrote: 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 -- Cheers, Ryan "Jay" wrote: Im clear on what I want to do, but I dont 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to calculate hours per work order
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. "RyanH" wrote: 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(strDestina tionWksName) Set wbkSource = Workbooks.Open("H:\FAC\DaveSipes\" & strSourceWbkName, UpdateLinks:=False, ReadOnly:=True) Set wksSource = Workbooks(strSourceWbkName).Sheets(strSourceWksNam e) 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 -- Cheers, Ryan "Jay" wrote: 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").Work sheets("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").Work sheets("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 "RyanH" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to calculate hours per work order
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(strDestina tionWksName) Set wbkSource = Workbooks.Open("H:\FAC\DaveSipes\" & strSourceWbkName, UpdateLinks:=False, ReadOnly:=True) Set wksSource = Workbooks(strSourceWbkName).Sheets(strSourceWksNam e) 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 -- Cheers, Ryan "Jay" wrote: 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. "RyanH" wrote: 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(strDestina tionWksName) Set wbkSource = Workbooks.Open("H:\FAC\DaveSipes\" & strSourceWbkName, UpdateLinks:=False, ReadOnly:=True) Set wksSource = Workbooks(strSourceWbkName).Sheets(strSourceWksNam e) 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 -- Cheers, Ryan "Jay" wrote: 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").Work sheets("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").Work sheets("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 "RyanH" wrote: 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") |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to calculate hours per work order
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" "RyanH" wrote: 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(strDestina tionWksName) Set wbkSource = Workbooks.Open("H:\FAC\DaveSipes\" & strSourceWbkName, UpdateLinks:=False, ReadOnly:=True) Set wksSource = Workbooks(strSourceWbkName).Sheets(strSourceWksNam e) 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 -- Cheers, Ryan "Jay" wrote: 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. "RyanH" wrote: 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(strDestina tionWksName) Set wbkSource = Workbooks.Open("H:\FAC\DaveSipes\" & strSourceWbkName, UpdateLinks:=False, ReadOnly:=True) Set wksSource = Workbooks(strSourceWbkName).Sheets(strSourceWksNam e) 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 -- Cheers, Ryan "Jay" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to calculate hours per work order
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to calculate hours per work order
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to calculate hours per work order
Yes, that worked wonderfully!
Thank you, thank you, thank you. "RyanH" wrote: 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. -- Cheers, Ryan "Jay" wrote: Thanks for your offer. I just sent you the workbooks. "RyanH" wrote: 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. -- Cheers, Ryan "Jay" wrote: 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" "RyanH" wrote: 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(strDestina tionWksName) Set wbkSource = Workbooks.Open("H:\FAC\DaveSipes\" & strSourceWbkName, UpdateLinks:=False, ReadOnly:=True) Set wksSource = Workbooks(strSourceWbkName).Sheets(strSourceWksNam e) 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 -- Cheers, Ryan "Jay" wrote: 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. "RyanH" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Time during different sets of work hours | Excel Worksheet Functions | |||
How do I calculate WORK-hours between end/start date | Excel Discussion (Misc queries) | |||
Apply a macro to all sheets - save me from hours and hours of work | Excel Programming | |||
calculate hours on work sheet | Excel Worksheet Functions | |||
Calculate work hours between two dates | Excel Worksheet Functions |