Type mismatch

[VegasNath]

Hi,

I am getting a 'type mismatch error on the vlookup here but I don't understand why?

Code: Select all

    Dim rng As Range, vlook As Range
    Dim cnt As Integer
    Dim fnd As String

    Set vlook = Sheets("Bins").Range("M1:N17")
    Set dstrg = dstws2.Range("C2:C" & dstlr)
    
    For Each rng In dstrg
        If rng.Offset(0, 1) = "" Then
            rng.Offset(0, 1) = "'10"
            rng.Offset(0, 1).Font.Bold = True
            cnt = cnt + 1
        End If
        If Not rng.Offset(0, 1) = "10" And Not Right(rng, 1) = "E" Then
            fnd = Application.VLookup(Left(rng, 6), vlook, 2, False)
                If IsError(fnd) Then
                    rng.Offset(0, 1) = "'10"
                    rng.Offset(0, 1).Font.Bold = True
                    cnt = cnt + 1
                End If
        End If
    Next rng

Thanks

[HansV]

If there is no match, Application.VLookup returns an error value. You can't assign an error value to a string variable. You must change the declaration of fnd to

Dim fnd As Variant

A Variant can be any data type, including string and error values.

[VegasNath]

Thanks Hans, that makes sense.