finding cells with wildcards

[nova30]

Hi,

I'm writing a macro for excel in which I need to select the cells based on the text within certain cells. However, the problem is that a proportion of the text is variable and therefore I cannot search for cells using hard-wired code. I hope the following example illustrates the problem.

The cell contains a few constant words "Primary Catchment -" and also variable words which are produced from some reporting software. These words are put in the same cell after the dash. I therefore need to find a way of searching for the cell using the constant words within the cell.

Example 1.

Primary Catchment - Test

Example 2.

Primary Catchment - Test1

I hope this makes sense!!


Nova30

 

[tusharm]

I'm not sure I understand what you want, but try the foll.

Turn on the macro recorder (Tools | Macro > Record new macro...), do whatever you want to do, and turn off the recorder. XL will generate code that should serve as a good starting point.

 

[Richie(UK)]

Hi nova30,

Welcome to the board.

See if this helps start you in the right direction.

<pre>
Sub findit()
Dim FindMe As String, c As Range, TestArea As Range, firstaddress

FindMe = "Primary Catchment" & "*"
Set TestArea = ThisWorkbook.Sheets("Sheet1").Range("A1:L30")

With TestArea
Set c = .Find(What:=FindMe, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Interior.ColorIndex = 4
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With

Set TestArea = Nothing
Set c = Nothing

End Sub

</pre>

 

[nova30]

Thankyou for you help but It does not seem to work. Is there a way to search for a selection of characters in a cell rather than the whole words? Someone has suggested using a function called Mid$, but i have now idea what that is at this stage.

 

[Richie(UK)]

Hi,

Two things:

1. You say that the routine does not work. I tested it using the examples you gave and it worked for me :wink: . In what way does it not work.

2. Re Mid$ - have you tried using the VBE Help? Broadly you could assign a string to be, say, =Mid(Your string,Start Position,Length of desired string).

The following is from the Help file:
This example uses the Mid function to return a specified number of characters from a string.

Dim MyString, FirstWord, LastWord, MidWords
MyString = "Mid Function Demo" ' Create text string.
FirstWord = Mid(MyString, 1, 3) ' Returns "Mid".
LastWord = Mid(MyString, 14, 4) ' Returns "Demo".
MidWords = Mid(MyString, 5) ' Returns "Function Demo".

 

[Andrew Poulsom]

You can find the variable constants using the Right Function like this:

Sub Test
    Const Words As String = "Primary Catchment - "
    Dim c As Range
    Dim Text As String
    Set c = Cells.Find(What:=Words, After:=Range("A1"), LookIn:= _
        xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
        xlNext, MatchCase:=False)
    Text = Right(c.Text, Len(c.Text) - Len(Words))
End Sub

 

[nova30]

Hi Richie,

I tried the code with various words after the dash in Primary catchments, but it did not pick the words up:-

Here is the code that i am working with:

Public Sub PostStoreOff()
Dim cStart As Range
Dim cEnd As Range

' first look for the first cell with Primary Catchment -* in it
Set cStart = Range("A1")
' now check for the number cell
While Not LCase(cStart.Value) = "primary catchment - new sites" ' It is here where I need it to search for just the Primary Catchment - (as the new sites bit changes in different books)

' set the cEnd to the next cell
Set cStart = cStart.Offset(1, 0)
Wend
Set cEnd = cStart.Offset(-1, 0)
Range("A" & cEnd.Row & ":" & "H" & cEnd.Row).Select
Selection.Interior.ColorIndex = xlNone

End Sub

I hope this helps you understand what I need.

Cheers,

Nova30

 

[nova30]

Hi Richie,

I tried the code with various words after the dash in Primary catchments, but it did not pick the words up:-

Here is the code that i am working with:

Public Sub PostStoreOff()
Dim cStart As Range
Dim cEnd As Range

' first look for the first cell with Primary Catchment -* in it
Set cStart = Range("A1")
' now check for the number cell
While Not LCase(cStart.Value) = "primary catchment - new sites" ' It is here where I need it to search for just the Primary Catchment - (as the new sites bit changes in different books)

' set the cEnd to the next cell
Set cStart = cStart.Offset(1, 0)
Wend
Set cEnd = cStart.Offset(-1, 0)
Range("A" & cEnd.Row & ":" & "H" & cEnd.Row).Select
Selection.Interior.ColorIndex = xlNone

End Sub

I hope this helps you understand what I need.

Cheers,

Nova30

 

[nova30]

Hi Richie,

I tried the code with various words after the dash in Primary catchments, but it did not pick the words up:-

Here is the code that i am working with:

Public Sub PostStoreOff()
Dim cStart As Range
Dim cEnd As Range

' first look for the first cell with Primary Catchment -* in it
Set cStart = Range("A1")
' now check for the primary catchment- * cell
While Not LCase(cStart.Value) = "primary catchment - new sites" ' It is here where I need it to search for just the Primary Catchment - (as the new sites bit changes in different workbooks)

' set the cEnd to the next cell
Set cStart = cStart.Offset(1, 0)
Wend
Set cEnd = cStart.Offset(-1, 0)
Range("A" & cEnd.Row & ":" & "H" & cEnd.Row).Select
Selection.Interior.ColorIndex = xlNone

End Sub

I hope this helps you understand what I need.

Cheers,

Nova30

 

[nova30]

Hi Richie,

I tried the code with various words after the dash in Primary catchments, but it did not pick the words up:-

Here is the code that i am working with:

Public Sub PostStoreOff()
Dim cStart As Range
Dim cEnd As Range

' first look for the first cell with Primary Catchment -* in it
Set cStart = Range("A1")
' now check for the primary catchment- * cell
While Not LCase(cStart.Value) = "primary catchment - new sites" ' It is here where I need it to search for just the Primary Catchment - (as the new sites bit changes in different workbooks)

' set the cEnd to the next cell
Set cStart = cStart.Offset(1, 0)
Wend
Set cEnd = cStart.Offset(-1, 0)
Range("A" & cEnd.Row & ":" & "H" & cEnd.Row).Select
Selection.Interior.ColorIndex = xlNone

End Sub

I hope this helps you understand what I need.

Cheers,

Nova30