Type Mismatch Error

Hello,

I am very new to VBA Programing and am still learning my way around things. I know that there is a Type Mismatch error on the Clocksum line but am not sure why. Any tips, pointers, or fixes would be greatly appreciated!

Sub find_problems()

Dim clocknum As Long
Dim ppe As Date
Dim pcode As Long
Dim weeknumber As String
Dim tothrs As Long
Dim clocksum As Long
Dim title1 As String
Dim jobnum As String


Set rclock = range("A2:A10000")   'clock number
Set rppe = range("F2:F10000")   'ppe
Set rhrs = range("Q2:Q10000")   'total hours
Set rweek = range("R2:R10000")   'week

Do While IsEmpty(Selection.Value) = False
    
    clocknum = Selection.Value 'ID Number
    ppe = Selection.Offset(0, 5).Value 'Date
    pcode = Selection.Offset(0, 8).Value 'Pay Code
    tothrs = Selection.Offset(0, 16).Value 'Hours
    weeknumber = Selection.Offset(0, 17).Value 'Week Number
    title1 = Selection.Offset(0, 3).Value 'Job Title
    jobnum = Selection.Offset(0, 6).Value 'Job Number
    
    clocksum = Application.WorksheetFunction.SumProduct((rclock = clocknum) * (rweek = weeknumber) * (rppe = ppe) * (rhrs))

    If clocksum > 120 And (Left(title1, 2) = "FF" Or Left(jobnum, 2) = "asst") Then
        
        Selection.EntireRow.Interior.Color = 50000
        
    ElseIf clocksum <= 40 Then
        
        If pcode = 4242 And tothrs = clocksum Then
            ActiveCell.Offset(1, 0).range("A1").Select
        Else
            Selection.EntireRow.Interior.Color = 50000
        End If
        
    ElseIf clocksum >= 41 And clocksum <= 72 Then
        
        If pcode = 4242 And tothrs = 40 Then
            ActiveCell.Offset(1, 0).range("A1").Select
        ElseIf pcode = 4000 And tothrs = clocksum - 40 Then
            ActiveCell.Offset(1, 0).range("A1").Select
        Else
            Selection.EntireRow.Interior.Color = 50000
            ActiveCell.Offset(1, 0).range("A1").Select
        End If
    ElseIf clocksum >= 73 And clocksum <= 84 Then
        If pcode = 4242 And tothrs = 40 Then
            ActiveCell.Offset(1, 0).range("A1").Select
        ElseIf pcode = 4000 And tothrs = clocksum - 40 Then
            ActiveCell.Offset(1, 0).range("A1").Select
        ElseIf pcode = 8005 And tothrs = clocksum - 72 Then
            ActiveCell.Offset(1, 0).range("A1").Select
        Else
            Selection.EntireRow.Interior.Color = 50000
            ActiveCell.Offset(1, 0).range("A1").Select
        End If

            
        
    End If
    
Loop
    

    
End Sub

Do you have some sample data that I can throw onto the sheet so that it doesn't fall out of the If loop? It runs right through for me without any data.

What do you have in mind for this(code/worksheet) to do for you?
Add up all of the hours for each clocknum, then highlight a row that meets the criteria in the loops?

edit: clocksum = 0 but I think it is because the SUMPRODUCT(A2,F2,Q2,R2) isn't giving you what you want. Doing this manually you get a VALUE# error because R2 is the wrong type of data.

Decoderman,

I'm going through this thread trying to find what the issue is. At one point you were asked what the purpose of the macro is:

Originally Posted by Decoderman

The worker has a set number of hours that they should be working. If they work less than that I want to see if they assigned it to the correct Pay Code:

4242 : 40 hrs
4000: 32 hrs
8005: 12 hrs

In that order. The FF Man has a different set of hours.

Does that clear anything up?

If it isn't charged correctly I want the entire row highlighted.

So I looked at the TestData you attached, and what I'm seeing is that you want the macro to do the following:

• Check the total hours of each employee

• If the total hours >40, the paycode should be 4242
• If 40 > totalhours > 32, the paycode should be 4000
• If 32 > totalhours > 12, the paycode should be 8005

• If any of the paycodes are not correct, highlight the entire row

With that information, I don't see that sumproduct is necessary at all. Here's the code I came up with to make that happen. Attached is a modified version of your TestData so you can see how it works.

Sub find_problems()

    Application.ScreenUpdating = False
    
    'Get lastline and initialize loop variables
    Dim LastWorker As Long: LastWorker = ActiveSheet.range("A" & Rows.Count).End(xlUp).Row
    Dim CheckHours As Long: CheckHours = 2
    
    'Start the loop
    While CheckHours <= LastWorker
        
        'Check the total hours
        Select Case ActiveSheet.range("Q" & CheckHours).Value
            Case Is >= 40
                'Hours found to be 40+, paycode should be 4242
                If ActiveSheet.range("I" & CheckHours).Value <> 4242 Then
                    'Found not to be 4242, highlights row
                    With ActiveSheet.Rows(CheckHours & ":" & CheckHours).EntireRow.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 65535
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                Else
                    'Found to be correct, unhighlights row
                    With ActiveSheet.Rows(CheckHours & ":" & CheckHours).EntireRow.Interior
                        .Pattern = xlNone
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                End If
                
            Case Is >= 32
                'Hours found to be 32-40, paycode should be 4000
                If ActiveSheet.range("I" & CheckHours).Value <> 4000 Then
                    'Found not to be 4000, highlights row
                    With ActiveSheet.Rows(CheckHours & ":" & CheckHours).EntireRow.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 65535
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                Else
                    'Found to be correct, unhighlights row
                    With ActiveSheet.Rows(CheckHours & ":" & CheckHours).EntireRow.Interior
                        .Pattern = xlNone
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                End If
                
            Case Is >= 12
                'Hours found to be 12-32, paycode should be 8005
                If ActiveSheet.range("I" & CheckHours).Value <> 8005 Then
                    'Found not to be 8005, highlights row
                    With ActiveSheet.Rows(CheckHours & ":" & CheckHours).EntireRow.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 65535
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                Else
                    'Found to be correct, unhighlights row
                    With ActiveSheet.Rows(CheckHours & ":" & CheckHours).EntireRow.Interior
                        .Pattern = xlNone
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                End If
                
        End Select
        
        CheckHours = CheckHours + 1
    Wend
    
    Application.ScreenUpdating = True
    
End Sub

Let me know if I have it wrong or the macro needs to do something else/more (I wasn't sure what you meant by "The FF Man has a different set of hours").

Hope this helps,
~tigeravatar

re: =SUMPRODUCT(A2,F2,Q2,R2)

R2 = "WK1" and is a string, so how does that work in the SUMPRODUCT formula?

I think this is what you were after originally:

Sub find_problems()

   Dim clocknum As Long
   Dim ppe As Date
   Dim pcode As Long
   Dim weeknumber As String
   Dim tothrs As Long
   Dim clocksum As Long
   Dim title1 As String
   Dim jobnum As String
   Dim lngLastRow As Long
   Dim lngRow As Long
   Dim strFormula As String

   With Sheets("Data")
      lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
      For lngRow = 2 To lngLastRow
         clocknum = .Cells(lngRow, "A").Value   'ID Number
         ppe = .Cells(lngRow, "F").Value   'Date
         pcode = .Cells(lngRow, "I").Value   'Pay Code
         tothrs = .Cells(lngRow, "Q").Value   'Hours
         weeknumber = .Cells(lngRow, "R").Value   'Week Number
         title1 = .Cells(lngRow, "D").Value   'Job Title
         jobnum = .Cells(lngRow, "G").Value   'Job Number

         strFormula = "SumProduct((A2:A" & lngLastRow & "=" & clocknum & ")*(R2:R" & lngLastRow & "=""" & _
                     weeknumber & """)*(F2:F" & lngLastRow & "=" & CLng(ppe) & ")*Q2:Q" & lngLastRow & ")"
         clocksum = Evaluate(strFormula)

         Select Case clocksum
            Case Is > 120
               If Left(title1, 2) = "FF" Or Left(jobnum, 2) = "asst" Then .Rows(lngRow).Interior.Color = 50000
            Case Is <= 40

               If pcode <> 4242 Or tothrs <> clocksum Then .Rows(lngRow).Interior.Color = 50000

            Case 41 To 72

               If pcode = 4242 And tothrs = 40 Then
                  ' do nothing
               ElseIf pcode = 4000 And tothrs = clocksum - 40 Then
                  ' do nothing
               Else
                  .Rows(lngRow).Interior.Color = 50000
               End If
            Case 73 To 84
               If (pcode = 4242 And tothrs = 40) Or (pcode = 4000 And tothrs = clocksum - 40) Or (pcode = 8005 And tothrs = clocksum - 72) Then
                  ' do nothing
               Else
                  .Rows(lngRow).Interior.Color = 50000
               End If

         End Select

      Next lngRow
   End With

End Sub