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