Freddie has attended:
Excel VBA Introduction course
IF Function
VBA Introduction:
Hi guys,
I am trying to create a code that in the event the 2 vlookups fail, then it will then search the cell for certain texts: these being:
NULL
Bank
Company
If the texts are found, then the activecell value should equal "Bank"
Currently I have the below and it is not working
=IFERROR(VLOOKUP('CPARTY CATEGORIES'!D:E,2,0),VLOOKUP(AE11,'CPARTY CATEGORIES'!C:E,3,0),if(SEARCH(E11,"*ICBC*",1),"BANK","Unknown"))
Any ideas?
Thanks
Freddie
RE: IF Function
VBA Intermediate Excel
Apologies guys, the code I have is the below but doesn't seem to work?
=IFERROR(VLOOKUP('CPARTY CATEGORIES'!D:E,2,0),VLOOKUP(AE1,'CPARTY CATEGORIES'!C:E,3,0)*IF(COUNT(SEARCH({"Bank","Sky High","BRANCH"},E2)),"BANK",IF(ISNUMBER(SEARCH("NULL",E2)),"NON FIN CUST","CORP")))
RE: IF Function
Hi Freddie,
Thank you for the forum question.
To use a worksheet function in VBA you will need to know how. Some worksheet functions can be typed as you would do in the worksheet some need more. Some need the word Application in front of the worksheet function name (Application.Match) the Vlookup needs Application.WorksheetFunction.Vlookup((AE11,'CPARTY CATEGORIES'!C:E,3,0).
The Iferror function can only work with two arguments. You will need an If decisions code to test if the Vlookups return #N/A and you will need an Array Function to work with an array in VBA or you can create an Array. You cannot use {}.
Dim MyArr(2) as string
will create an array in which you can store the three text strings "Bank","Sky High","Branch"
I hope this can help you in the right direction.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
