Searching cells and IF Function

Resolved · High Priority · Version 2010

Thu 16 Apr 2015, 14:09 Delegate Freddie said...

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

Fri 17 Apr 2015, 10:40 Edited on Fri 17 Apr 2015, 10:43 Trainer Doug said...

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

Tue 21 Apr 2015, 11:39 Delegate Freddie said...

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

Wed 22 Apr 2015, 11:43 Trainer Doug said...

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

Thu 30 Apr 2015, 12:07 Delegate Freddie said...

RE: Searching cells and IF Function

Hi Guys

Did you have any luck in trying to find a solution to my query?

Thanks

Freddie

Tue 19 May 2015, 13:55 Trainer Jens said...

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