if iserror vlookup

[nicnad]

Hi,

I am trying to write a vlookup function in Excel but nothing is working.

All I want it to do is return the value of the vlookup if it finds one or return "" if it find nothing (#!VALUE,#!N/A)

Here is my code so far :

Function date_test(country As String) As Variant
date_test= Application.WorksheetFunction.VLookup(country _
, Worksheets("data").Range("B12", "m999"), 12, False)
If IsError(date_test) Then date_test = ""

What is the right way to write this?

Thank you for your help.

 

[p45cal]

try changing:
Range("B12", "m999")
to:
Range("B12:m999")
(actually, on reflection, that bit may be all right)

Right, try:

Function date_test(country As String) As Variant
date_test = Application.VLookup(country, Worksheets("data").Range("B12", "m999"), 12, False)
If IsError(date_test) Then date_test = ""
End Function

(removed 'worksheetfunction')

 

[Gerald Higgins]

Not sure but maybe replace

Range("B12", "m999")

with

Range("B12:M999")

 

[nicnad]

Thank you for your quick replies.

It returns #!VALUE.

Any idea why?

 

[p45cal]

Difficult to see how or why. Could you post your code as is now, and give a few more details as to what's in the table you're looking in and what values you're expecting, and what value you're supplying.

 

[nicnad]

This is my entire code.

I need to return a date.

The date within the sheets "SANCTIONS" and "FATF OSFI WARNINGS" changes depending on what the country is. This is why I need to do a vlookup with the country (i.e. country as string, the user choose the cell that contains the country name)

The other sheets contain a date on range("b3") or range("b4") that is common for all country.

I then want to return the latest date within all those sheets for the selected country.

Hope this is clear.

Function lastupdate_max(country As String) As Variant
 
date_sanction = Application.WorksheetFunction.VLookup(country, Worksheets("SANCTIONS").Range("a7:k999"), 9, False)
If IsError(date_sanction) Then date_sanction = ""

date_fatf_warning = Application.WorksheetFunction.VLookup(country, Worksheets("FATF OSFI WARNINGS").Range("B12:m999"), 12, False)
If IsError(date_fatf_warning) Then date_fatf_warning = ""

date_wgi = Worksheets("WGI").Range("b3").Value
If IsError(date_wgi) = True Then date_wgi = ""
 
date_fatf_member = Worksheets("FATF MEMBERS").Range("b3").Value
If IsError(date_fatf_member) Then date_fatf_member = ""

date_tax_haven = Worksheets("OFFSHORE & TAX HAVENS").Range("b4").Value
If IsError(date_tax_haven) Then date_tax_haven = ""
 
date_cpi = Worksheets("CPI").Range("b3").Value
If IsError(date_cpi) Then date_cpi = ""

lastupdate_max = Application.WorksheetFunction.Max(date_sanction, date_fatf_warning _
, date_wgi, date_fatf_member, date_tax_haven_date_cpi)
End Function

Again, thank you all for your help!

 

[p45cal]

I was more interested to see your current code for the date_test function. You're doing the right thing by simplifying the problem. I see in the other code you still have lots of 'worksheetfunction'.
So is the date_test function returning #Value?

If you're calling these functions from a formula in the sheet it will jump out without giving an error if one is met. Instead, call the function from the immediate pane with the likes of:
?date_test("england")
or whatever country. Have a break point on the first line of the function, then step through the code with F8. You'll soon know where it errors.

or write a little test macro:

sub test()
x=date_test("england")
msgbox x
end sub

 

[nicnad]

I tried the msgbox trick and I get an error. Any idea what is causing this?

 

[p45cal]

Well, you will know because as you step throuigh the code you'll see exactly which line is giving the problem. "get an error" is insufficient information.