CODEWITHSUNDEEP
vbaexcel
Clauric
Clauric

Reputation: 1886

Application.VLookup format

Having a problem with Application.VlookUp in VBA.

I'm running the following code to try and get an equivalency

Dim hh As Variant
Dim hi As Variant

hh = Range("A" & hd)
hi = Range("B" & hd)

If hi = Application.VLookup(hh, Worksheets("Equivalency Table").Range("A1:D20"), 2, False) Then

If Not IsError(hi) Then
   Range("A" & hd & ":B" & hd).Select
   Selection.Style = "Good"
   hf = hf + 1

Else
   Range("A" & hd & ":B" & hd).Select
   Selection.Style = "Bad"

End If

hd is an incrementing variable

When I run it, the program should check the equivalency table, and if the value "hh" is found in column A, then it should return the value in column D. The program then checks if the value in "hi" is equal to the value returned by the VLookUp.

However, when I run the program, I get the following error:

Run time error: 13 Type mismatch

I've tried to change the DIM of "hi" to String, Long, and Integer but no luck. The value of "hh" can either be letters, numbers, or a combination, while "hi" will always be numbers.

EDIT:

After a bit more fiddling with the files, the error now only seems to come through when the VLookUp can't find the value of "hh" in column A of the equivalency table

Upvotes: 1

Views: 119

Answers (1)

mielk
mielk

Reputation: 3940

If VLOOKUP cannot find the value and returns error, this command:

hi = Application.VLookup(hh, Worksheets("Equivalency Table").Range("A1:D20"), 2, False)

tries to compare primitive value with error value and this causes Run-time error 13: Type mismatch.

In order to avoid this issue I suggest you introduce another variable VLookupResult. First assign the result ot VLOOKUP function into this variable and before comparing it to hi check if it is not error.

Below is the code with those changes:

Sub x()
    Dim hh As Variant
    Dim hi As Variant
    Dim VLookupResult As Variant

    hh = Range("A" & hd)
    hi = Range("B" & hd)
    VLookupResult = Application.VLookup(hh, Worksheets("Equivalency Table").Range("A1:D20"), 2, False)

    If IsError(h1) Then
        Range("A" & hd & ":B" & hd).Select
        Selection.Style = "Bad"
    ElseIf hi = VLookupResult Then
        Range("A" & hd & ":B" & hd).Select
        Selection.Style = "Good"
        hf = hf + 1
    End If

End Sub

Upvotes: 3

Related Questions