String Search

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
I have a sheet with several Named Ranges.
What would be the best way to search these ranges for a string input by user?
Once the String is found is it possible to return the Named Range it is located in?
e.g. User inputs HFB-M01, HFB-MO1 is located in a named range HFN1, (but is in the 4th column of named range), can vb code find the string located in a named range and return the name of the named range or at least the cell location?

Thanks for help, I seemed to have thought myself into a corner and can't think any more...
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi Chas,

The answer is yes, this can be easily done. But in order to suggest specific code some clarification would be helpful. In particular:

1. Do you want to search ALL named ranges on the worksheet, or just specific named ranges? If specific named ranges, does the user specify them as well?

2. What if the string is not in any named range?

3. What if the string is in multiple named ranges? This could be because the string occurs multiple times (i.e., in different cells), or one time in overlapping ranges.

4. Are you seeking a user-defined function (UDF), or a macro? Or just a code snippet to incorporate into a userform commandbutton Click event?

Damon
 

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
Answers:
1. Not sure what's best, Say there are 15 named ranges which can be in three Groups, i.e. 3 Units, each with 5 named Ranges. I could use select case to seach specific depending on user input for Unit. User would "Not" specify named range.

2. I would handle this with error code or validation or pick list, assume it will be there.

3. Will not be (should not) in multiple ranges, they do not overlap.

4. just a code snippet to incorporate into a userform commandbutton Click event.

Thanks.
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Okay, Chas, here is some code:

Dim RangeGroup As Range
Dim RangeBlock As Range
Dim CellFound As Range
Dim Answer As String
Set RangeGroup = [month,monum,expanol]
Answer = InputBox("Enter text to find", "Range Search")
If Answer = "" Then Exit Sub
Set CellFound = RangeGroup.Find(Answer, LookIn:=xlValues, lookat:=xlPart)
If CellFound Is Nothing Then
MsgBox "Not found"
Exit Sub
End If
'what range did it come from
For Each RangeBlock In RangeGroup.Areas
If Not Intersect(RangeBlock, CellFound) Is Nothing Then
MsgBox "Text found in " & RangeBlock.Name.Name, vbInformation, "Location"
End If
Next RangeBlock

This code searches for the text the user inputs (put into the variable "Answer"), in the three ranges named "month", "monum" and "expanol". The resulting name of the block is RangeBlock.Name.Name, which can be placed in a label on your userform with code like:

Label1.Caption = "Text found in range block named: " & RangeBlock.Name.Name

Please note that RangeGroup is a range object that contains the entire group of named ranges of interest. An equivalent way of setting this would have been:

Set RangeGroup = Range("A5:B7,C2:C9,F1:G13")

where the three blocks are the three named ranges.

Does this answer the question?
 

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
Thank you, I believe it does.
Code:
Set RangeGroup = [month,monum,expanol]
and
Set CellFound = RangeGroup.Find(Answer, LookIn:=xlValues, lookat:=xlPart)
and
For Each RangeBlock In RangeGroup.Areas
If Not Intersect(RangeBlock, CellFound) Is Nothing Then
MsgBox "Text found in " & RangeBlock.name.name, vbInformation, "Location"
End If
Next RangeBlock

is what I was looking for.

I started to do it a round about way in the spreadsheet it self, but will give your code a go as it seems better for flexability and change later. I will let you know more by email. I like your web site. Chas.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,100
Messages
5,622,714
Members
415,922
Latest member
gemmatay88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top