Forum Discussion

Lorenzo Kim's avatar
Lorenzo Kim
Bronze Contributor
Aug 19, 2018

Excel formula used in VBA

the excel formula  =IFERROR(VLOOKUP(A2,MAIN!A2:B8,2,0),"")

works well in a cell - but How do I write it in VBA?

I came across an article saying to use WorksheetFunction.Application.IFERROR.....

but somehow it is not working.

many thanks

 

Macros and VBA

3 Replies

Sort By
  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Aug 19, 2018

    I think I found where the trouble was, it was in the error trapping.

    when Vlookup did not find any match it issued #N/A error -

    to solve, I did the following:

    On Error Resume Next
    Vlookup function code..
    On Error GoTo 0

     thank you..

    • Man Fai Chan's avatar
      Man Fai Chan
      Iron Contributor
      Aug 19, 2018

      For me, I prefer to use this worksheet function in another worksheet and then call the value. 

       

      I usually introduce a worksheet <Info> for doing this kind of work. For example, in range("B22") , I will insert the formula =iferror(vlookup(B21,E:F,2,false),0)

       

      So, in VBA, once I need to do such vlookup, I will put the value into Info.range("B21") first and then get the corresponding value/string from Info.range("B22"). In case of 0 return, it means the vlookup fails and hence I can write other procedure for this situation. 

       

Resources