Freddie has attended:
Excel VBA Introduction course
Searching cells and IF Function
Hi Guys
I'm using a vlookup for a large amount of data.
However when the result returned is "#N/A", I then wish to check if the activecell.offset (0,-1) contains the word "LME" in it at all (i.e. if it is USDLME or UKLME as the data is concatenated).
If it does, then I wish for the active cell value to be "INLBAMKTCF - EXCEPTION" if not then I need "ERROR". I only have the below and it's not working:
Sheets("LBAPARIF").Range("P2").Select
ActiveCell.Formula = "=IF(ISERROR(SEARCH(""LME"",Activecell.offset(0,-1))),""INLBAMKTCF - EXCEPTION"",""ERROR"")"
Do Until ActiveCell.Offset(0, -1) = ""
ActiveCell.Offset(1, 0).Select
Loop
End If
Are you able to help?
Thanks as always!
Freddie
RE: Searching cells and IF Function
Hi Freddie
I tried out your example code and managed to get it to work I think. Try it out and see.
Sheets("LBAPARIF").Range("P2").Select
Do Until ActiveCell.Offset(0, -1) = ""
ActiveCell.Formula = "=IF(ISERROR(SEARCH(""LME"",RC[-1])),""ERROR"",""INLBAMKTCF - EXCEPTION"")"
ActiveCell.Offset(1, 0).Select
Loop
These are the changes I made:
move the Do loop up a line
Change Activecell.offset(0,-1) to RC([-1]
Swap ""ERROR"" and ""INLBAMKTCF - EXCEPTION"" around
Remove Endif
Let me know how you get on.
Regards
Doug
Best STL
RE: Searching cells and IF Function
Hi Doug
Thanks for this. The above works fine, but I'm not getting the desired result.
Apologies, I might not have explained myself properly.
What I'm trying to achieve is that if there is an error with the Vlookup, then to search the activecell.offset(0,-1) for the text LME. I have something similar to the below:
Sheets("LBAPARIF").Select
Range("P2").Select
Do Until ActiveCell.Offset(0, -1) = ""
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],LBAMKTCF!C[-11]:C[-10],2,0)"
IF ISERROR Then(SEARCH(""LME"",RC[-1])),""ERROR"",""INLBAMKTCF - EXCEPTION"")
ActiveCell.Offset(1, 0).Select
Loop
But it keeps falling over. Are you able to help?
Thanks!
Freddie
RE: Searching cells and IF Function
Hi Freddie
This is an interesting question though getting too involved for a forum question.
I will refer to another trainer and sorry for no immediate solution.
Regards
Doug
RE: Searching cells and IF Function
Hi Guys
Did you have any luck in trying to find a solution to my query?
Thanks
Freddie
RE: Searching cells and IF Function
Hi Freddie,
I am sorry that you had to wait for an answer.
Interesting question.
Try this:
Sheets("LBAPARIF").Select
Range("P2").Select
Do Until ActiveCell.Offset(0, -1) = ""
ActiveCell.FormulaR1C1 = "=iferror(VLOOKUP(RC[-1],LBAMKTCF!C[-11]:C[-10],2,0),if(count(SEARCH(""*LME*"",RC[-1]))>0,""ERROR"",""INLBAMKTCF - EXCEPTION""))"
ActiveCell.Offset(1, 0).Select
Loop
I hope this will do what you want but please let me know if it doesn't.
End Sub
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
