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
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