Forum Discussion
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..... bu...
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..
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.
- thank you Mr. Chan for the tip..