String Search

[Chas17]

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...

 

[Damon Ostrander]

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]

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]

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]

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.