CODEWITHSUNDEEP
vbaexcel
ArsedianIvan
ArsedianIvan

Reputation: 399

VBA < Operator causes Type Mismatch Error

I am using the following code to isolate certain set of data based on two criterias, (1) Keyword (i.e User 1); (2) Nominal Value

The following code seem to work on Microsoft Excel 2013 however throwing me a mismatch error on Microsoft Excel 2010. Would be great if you could help.

The code as follow:

  Sub FinalApprover()
     lastRow = Sheets("Source").Cells(Rows.Count, 1).End(xlUp).Row

     For i = 2 To lastRow
        If Sheets("Source").Cells(i, 1).Value = "User 1" And ("Source").Cells(i, 2) < 50000 Then

          Sheets("Source").Cells(i, 3).Value = "Final Approver"
       End If
     Next

 End Sub

Upvotes: 0

Views: 571

Answers (1)

Archias
Archias

Reputation: 383

The error is within the values you are testing against from the worksheet. You most likely need to use a form of data validation to make sure you have String Values in Column 1 and Numeric Values in Column 2.

I believe a little error handling may help this scenario. Run this function in place of your old function and if the error is thrown again this function will write what row the error is on and what values you are comparing to your hardcoded String "User 1" & Integer 50000 and then pause execution. Once the program stops, then you will be able to see these values in the Immediate window by pressing "CTRL + G".

Sub FinalApprover_New()
    Dim r As Long, lLastRow As Long
    Dim wsSource As Worksheet
    Dim bDebug As Boolean

    Set wsSource = ThisWorkbook.Sheets("Source")

    With wsSource
        'lLastRow = wsSource.Cells(Rows.Count, 1).End(xlUp).Row
        lLastRow = .Rows.Count
        For r = 2 To lLastRow
            ' bDebug is used to attempt to compare the values ahead of time to check if an exception is thrown.
            bDebug = (.Cells(r, 1).Value = "User 1" And .Cells(r, 2) < 50000)
            If Err.Number <> 0 Then
                Debug.Print "Row: " & r & " | " & "Value 1: " & .Cells(r, 1).Value & " | Value 2: " & .Cells(r, 2).Value
                Debug.Assert 0 ' Hardcoded breakpoint
            End If
            If .Cells(r, 1).Value = "User 1" And .Cells(r, 2) < 50000 Then
                .Cells(r, 3).Value = "Final Approver NEW"
                Exit For
            End If
        Next
    End With
End Sub

Upvotes: 1

Related Questions