Mismatch Msgbox

[Ruca13]

Hi,

I'm using vlookup and sometimes I get the mismatch error because the cell it retrieves has an error (NA to be more precise), which is ok, but I want to get an msgbox and exit the sub when that happens.

This is the part of the code where I have the vlookup:

Dim price As Double


price = Application.VLookup(Location, lk_range, 10, False)


If IsError(price) Then
MsgBox ("The price is not available for that location")
ActiveWorkbook.Close
Exit Sub
End If

Thank you.

 

[steve the fish]

What does location and lk_range refer to?

 

[Ruca13]

Location is:

Location = Cells(ActiveCell.Row, 16).Value

It's location i'm searching. I've stored it as a variable because it's not in the file I'm searching.

lk_range:

Dim lk_range As Range

Set lk_range = Range(Cells(8, 2), Cells(Cells(8, 2).End(xlDown).Row, 15))

The file where I'm searching is not always the same, but the starting row and columns to search are the same, so the only flexible part is the last row of the range.

 

[Giordano Bruno]

Surely if price returns and error, the code will stop there and you will not be able to do anything further. Why not precede the lookup with On Error goto?

 

[steve the fish]

Change to:

Dim price As Variant

and the code should then run.

 

[Ruca13]

Thank you, it works just fine now.