results when step through, none when run

[ron]

I have a procedure that produces the results when I step through in vbe but when I run it my values are zero.


Sub total_productive_hours()
Dim x As Integer
Dim z As Integer
Dim LastRow As Integer
Dim TotProposalHrs As Double
Dim FirstWeek As Date
Dim colFRange As Range
Dim col As Integer
On Error Resume Next
LastRow = Worksheets("Sheet1").UsedRange.Rows.Count
col = 2
Set colFRange = Worksheets("Sheet1").Range("F2:F" & LastRow)
FirstWeek = Application.WorksheetFunction.Min(colFRange)
For z = 1 To 5
For x = 2 To LastRow
If FirstWeek = Cells(x, 6).Value Then
If Cells(x, 4).Value <> "HOL" And _
Cells(x, 4).Value <> "PTO" And _
Cells(x, 4).Value <> "PAO" Then
TotProposalHrs = Cells(x, 7).Value + TotProposalHrs
End If
End If
Next x
Worksheets("Sheet2").Cells(2, col).Value = TotProposalHrs
Worksheets("Sheet2").Cells(1, col).Value = FirstWeek
col = col + 1
FirstWeek = FirstWeek + 7
TotProposalHrs = 0
Next z
End Sub

Thanks

ron

[Rob342]

Ron,
Change this line
Dim LastRow As Long

Should this be "Or" & not and?

If Cells(x, 4).Value <> "HOL" Or _
Cells(x, 4).Value <> "PTO" Or _
Cells(x, 4).Value <> "PAO" Then

[Tommy]

I think I would take out the on Error Resume next and let it throw up, to see where it is going wrong

[ron]

Rob & Tommy

Thanks for the suggestions I will give them a shot and let your know.

Ron

[Paul_Hossler]

If Cells(x, 4).Value <> "HOL" And _
Cells(x, 4).Value <> "PTO" And _
Cells(x, 4).Value <> "PAO" Then


'Cells' used without a qualifer will use the active sheet, and often times generate erratic results

Try Worksheets("Sheet1").Cells ( ...) and see if it's more reliable

[mdmackillop]

Always use Long instead of Integer
Set Worksheet variables and qualify all ranges as suggested
Sub total_productive_hours()
Dim x As Long
Dim z As Long
Dim LastRow As Long
Dim TotProposalHrs As Double
Dim FirstWeek As Date
Dim colFRange As Range
Dim col As Integer
Dim Ws1 As Worksheet, Ws2 As Worksheet


Set Ws1 = Worksheets("Sheet1")
Set Ws2 = Worksheets("Sheet2")

'On Error Resume Next

With Ws1
LastRow = .UsedRange.Rows.Count
col = 2
Set colFRange = .Range("F2:F" & LastRow)
FirstWeek = Application.WorksheetFunction.Min(colFRange)
For z = 1 To 5
For x = 2 To LastRow
If FirstWeek = .Cells(x, 6).Value Then
If .Cells(x, 4).Value <> "HOL" And _
.Cells(x, 4).Value <> "PTO" And _
.Cells(x, 4).Value <> "PAO" Then
TotProposalHrs = .Cells(x, 7).Value + TotProposalHrs
End If
End If
Next x
Ws2.Cells(2, col) = TotProposalHrs
Ws2.Cells(1, col) = FirstWeek
col = col + 1
FirstWeek = FirstWeek + 7
TotProposalHrs = 0
Next z
End With

End Sub