CODEWITHSUNDEEP
vbaexcelsearchwildcard
gduhoffmann
gduhoffmann

Reputation: 39

VBA Excel using a wildcard and resolved object reference

I'm trying to get the search string typed in the inputbox to be used in conjunction with the * wildcard to search for instances of the string within the selected range. 

Sub color()
Dim myRange As Range, value As String, wild As Icon

value = InputBox("Search String:")
If value = vbNullString Then Exit Sub
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
For Each myRange In Selection
  If myRange.value = "*" & value & "*" Then
    myRange.Interior.ColorIndex = 3
  End If
Next myRange
End Sub

Upvotes: 0

Views: 1077

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Rather than wildcard:

Sub color()
   Dim myRange As Range, valuee As String
   valuee = InputBox("Search String:")
   If valuee = vbNullString Then Exit Sub
   Range("A1").Select
   Range(Selection, Selection.End(xlToRight)).Select
   Range(Selection, Selection.End(xlDown)).Select

   For Each myRange In Selection
      If InStr(myRange.value, valuee) > 0 Then
         myRange.Interior.ColorIndex = 3
      End If
   Next myRange
End Sub

We could also use the .Find method.

EDIT#1:

Here is a version using .Find and .FindNext:

Sub color2()
   Dim myRange As Range, valuee As String
   valuee = InputBox("Search String:")
   If valuee = vbNullString Then Exit Sub

   Range("A1").Select
   Range(Selection, Selection.End(xlToRight)).Select
   Range(Selection, Selection.End(xlDown)).Select

   Set myRange = Selection.Find(what:=valuee, after:=Selection(1))
   If myRange Is Nothing Then
      MsgBox "no value"
      Exit Sub
   End If
   myRange.Interior.ColorIndex = 3
   st = myRange.Address(0, 0)

   Do Until myRange Is Nothing
      Set myRange = Selection.FindNext(after:=myRange)
      If myRange.Address(0, 0) = st Then Exit Do
      myRange.Interior.ColorIndex = 3
   Loop
End Sub

Upvotes: 0

John Coleman
John Coleman

Reputation: 51988

Another possibility: Why use wildcards at all? There is already a VBA function to test for substrings. Try:

If InStr(myRange.value,value) > 0 Then

Upvotes: 3

Related Questions