ron
11-04-2011, 06:12 AM
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
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