Results 1 to 9 of 9

if iserror vlookup

This is a discussion on if iserror vlookup within the Excel Questions forums, part of the Question Forums category; Hi, I am trying to write a vlookup function in Excel but nothing is working. All I want it to ...

  1. #1
    Board Regular
    Join Date
    Sep 2011
    Posts
    199

    Default if iserror vlookup

    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 :

    Code:
    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.

  2. #2
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,764

    Default Re: if iserror vlookup

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

    Right, try:
    Code:
    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')
    Last edited by p45cal; Mar 1st, 2012 at 04:59 PM.

  3. #3
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    7,150

    Default Re: if iserror vlookup

    Not sure but maybe replace

    Range("B12", "m999")

    with

    Range("B12:M999")
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  4. #4
    Board Regular
    Join Date
    Sep 2011
    Posts
    199

    Default Re: if iserror vlookup

    Thank you for your quick replies.

    It returns #!VALUE.

    Any idea why?

  5. #5
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,764

    Default Re: if iserror vlookup

    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.

  6. #6
    Board Regular
    Join Date
    Sep 2011
    Posts
    199

    Default Re: if iserror vlookup

    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.

    Code:
    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!

  7. #7
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,764

    Default Re: if iserror vlookup

    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:
    Code:
    sub test()
    x=date_test("england")
    msgbox x
    end sub

  8. #8
    Board Regular
    Join Date
    Sep 2011
    Posts
    199

    Default Re: if iserror vlookup

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

  9. #9
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,764

    Default Re: if iserror vlookup

    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com