Thread: Similiar to VLOOKUP?

Hi all.

I am not sure which fuction to use, here is what I would like to acheive:

Is there a way to look for any cells which *contain* 'BM2E01N'? Not just the cells that are an exact match? Such as BM2E01N_06-07_Spring & BM2E01N_06-07_Autumn?

Basically I want to highlight all cells that contain a certain value.

Thank you.

Can you just use a VLOOKUP but put in TRUE instead of FALSE ?

Originally Posted by Editz

Hi all.

I am not sure which fuction to use, here is what I would like to acheive:

Is there a way to look for any cells which *contain* 'BM2E01N'? Not just the cells that are an exact match? Such as BM2E01N_06-07_Spring & BM2E01N_06-07_Autumn?

Basically I want to highlight all cells that contain a certain value.

Thank you.

somebody was just looking for something similar the other day

in this case select the cell with BM2E01N as the source and the cells you're searching for that within as your target

Sub FindWithin()

Dim Temp As String
Dim SourceSheet As Worksheet
Dim TargetSheet As Worksheet
Dim TargetRange As Range
Dim SourceRange As Range
Dim aRange As Range
Dim aText() As String

Set SourceRange = Application.InputBox(Prompt:="Select Source Range", Title:="Source Range", Type:=8)
Temp = SourceRange.Parent.Name
Set SourceSheet = Worksheets(Temp)



Set TargetRange = Application.InputBox(Prompt:="Select Target Range", Title:="Target Range", Type:=8)
Temp = TargetRange.Parent.Name
Set TargetSheet = Worksheets(Temp)

For Each Cell In SourceRange
Temp = Cell.Value
Count = 0
If Temp <> "" Then
aText() = Split(Temp, " ")
For Each elem In aText()
For Each tCell In TargetRange
Set aRange = tCell.Find(what:=elem, lookat:=xlPart, LookIn:=xlValues)
Next
Next
If Count = 0 Then
Cell.Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Else
Cell.Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
End If
End If
Next
End Sub

Originally Posted by Steel Monkey

Can you just use a VLOOKUP but put in TRUE instead of FALSE ?

I did try that but it didnt make any difference.

It just found the first instance of BM2E01N rather than highlighting all the cells with BM2E01N in.

Originally Posted by MDubbelboer

somebody was just looking for something similar the other day

in this case select the cell with BM2E01N as the source and the cells you're searching for that within as your target

Sub FindWithin()

Dim Temp As String
Dim SourceSheet As Worksheet
Dim TargetSheet As Worksheet
Dim TargetRange As Range
Dim SourceRange As Range
Dim aRange As Range
Dim aText() As String

Set SourceRange = Application.InputBox(Prompt:="Select Source Range", Title:="Source Range", Type:=8)
Temp = SourceRange.Parent.Name
Set SourceSheet = Worksheets(Temp)



Set TargetRange = Application.InputBox(Prompt:="Select Target Range", Title:="Target Range", Type:=8)
Temp = TargetRange.Parent.Name
Set TargetSheet = Worksheets(Temp)

For Each Cell In SourceRange
Temp = Cell.Value
Count = 0
If Temp <> "" Then
aText() = Split(Temp, " ")
For Each elem In aText()
For Each tCell In TargetRange
Set aRange = tCell.Find(what:=elem, lookat:=xlPart, LookIn:=xlValues)
Next
Next
If Count = 0 Then
Cell.Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Else
Cell.Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
End If
End If
Next
End Sub

Sorry, I don't understand what to do with that.

Originally Posted by Editz

Hi all.

I am not sure which fuction to use, here is what I would like to acheive:

Is there a way to look for any cells which *contain* 'BM2E01N'? Not just the cells that are an exact match? Such as BM2E01N_06-07_Spring & BM2E01N_06-07_Autumn?

Basically I want to highlight all cells that contain a certain value.

Thank you.

with a formula:
assuming you're only looking for BM2E01N and it's in cell A1
and you're searching through values in column B

highlight column B, go into conditional formatting and enter the following formula
"=IF(ISERROR(FIND($A$1,B1,1)),FALSE,TRUE)"
this assumes that B1 is your active cell (i.e. you selected column B by clicking B1 and dragging down to the last value in column B)
within conditional formatting go to "format" and choose patterns and the color you want highlighted

Editz,

Select your range say A1:A10. Go to Format>Conditional Formatting. Change the Cell Value is to Formula is and enter:

=ISNUMBER(FIND("BM2E01N",A1))

Click on Format>Patterns and select the color you want to fill the cells with. Click OK and OK. This will highlight the cells that contain the string.

HTH

Steve

Hi,

Following should do the job ...you are looking for strings in strings

Function FindWord(sWord As String, sText As String) As Boolean
Dim re As RegExp
Set re = New RegExp
re.IgnoreCase = True
re.Pattern = "\b" & sWord & "\b"
FindWord = re.Test(sText)
End Function

Do not forget to tick off in the VBE references :
Microsoft VBScript Regular Expressions 1.0

HTH
Cheers
Carim

Thanks but I don't know how to work with VBA.

Hi,

1. Alt F11
Insert Module
In module 1 copy the code

Function FindWord(sWord As String, sText As String) As Boolean
    Dim re As RegExp
    Set re = New RegExp
    re.IgnoreCase = True
    re.Pattern = "\b" & sWord & "\b"
    FindWord = re.Test(sText)
End Function

2. from menu Tools References
select Microsoft VBScript Regular Expressions 1.0

That's it ...
You can now use the function in your spreadsheet ...
=Findword(Masterword, variableWord)

HTH
Carim

Originally Posted by Editz

Hi Steve,

Thanks for that it does work but it doesnt quite do what I need.

I should have made it clearer, its not just BM2E01N I want to find.

I have two columns of values:

A:

BM2E01N
BM2E01N_06-07_Autumn
CD2E01N
EF2E01N
EF2E01N_06-07_Spring

B:
BM2E01N
CD2E01N
EF2E01N

B contains the master list and A contains that list plus other codes that have been expanded.

I want to do a lookup on column A to find values in column B *but* if the value in B has an extra bit on the end i.e. '_06-07_Spring' then I want to lookup that cell rather than the one that is the exact match.

Does that make sense?

I suppse that your data is as follows

col A:

BM2E01N
BM2E01N_06-07_Autumn
CD2E01N
EF2E01N
EF2E01N_06-07_Spring

col B:

BM2E01N
CD2E01N
EF2E01N

and you want to search values of column B in column A and highlight cells in column A if it contains value from any cell of col B (partly of fully)

select range of values in col A and go to Conditional Formatting and use following formula.

=IF(ISERROR(MATCH(IF(ISERROR(MID(A1,1,FIND("_",A1)-1)),A14,MID(A1,1,FIND("_",A1)-1)),$B$1:$B$50)),"false","true")

this formula suppose that you have values in col B in a range of B1:B50 (you can extend this range) which you want to look in col A and values in col A always contain values of col B in the start of text string before an "_"

Regards.

A completely different approach, but would a filter (either auto or advanced) do what you're looking for? see Data->Filter->select desired filter type.

Originally Posted by MrShorty

A completely different approach, but would a filter (either auto or advanced) do what you're looking for? see Data->Filter->select desired filter type.

Not in any way that I can think of.

I have attached a file that might explain it better than I can with text.