Mask search with *

[Becks7]

Dim myStr As String, j As Long
Dim nextRow As Long
Dim splStr As Variant, match As Variant
Dim copyrow As Boolean

myStr = UCase(Me.tbSearchText.Text)
splStr = Split(myStr, "*")

For j = UBound(splStr) To 0
	match(j) = InStr(1, UCase(.Cells(1, 1).Value), splStr(j))
Next j
For j = UBound(splStr) To 1
	If match(j) > match(j-1) Then
		copyrow = True
	Else:
		copyrow = False
	End If
Next j
If copyrow = True Then
	'Copy row code
End If

I'm trying to make a mask search support with '*' but there is something wrong with this code. I get a "Type mismatch" error

[Richard Schollar]

I don't see anywhere that you are dimensioning your array 'match', also you need to explicitly state what your step value is in your For loops (as you are, I presume, trying to loop backwards).

I think it would also really help if you could explicitly explain what it is you are trying to do.

Richard

[Becks7]

I have a workbook very similar to the one attached. I've made a simple search function - it checks if a string is contained into one of the 2 text fields on Sheet1. I want to make it more advanced and add mask search with '*' and '?'.
For example, I would get "Peter Huddlestone" if I enter in the Search field:
pet*
p*er
p?t?r
hud?le*t?ne
...
etc

[Becks7]

... any hints ?

[Leith Ross]

Hello Becks7,

I added the following macro to the attached workbook. The macro has been expanded to allow the user to choose if case is to be ignored in the search. A check box has been added for this purpose.

Private Sub CommandButton1_Click()
  Call FindText
End Sub

Sub FindText()

  Dim Cell As Range
  Dim ChkBox As Object
  Dim DstWks As Worksheet
  Dim I As Long
  Dim LastRow As Long
  Dim R As Long
  Dim Rng As Range
  Dim StartRow As Long
  Dim TxtBox As Object
  
    
    Set DstWks = Worksheets("Search")
    
   'Find the Next Row and set object variables
    With DstWks
      StartRow = 7
      R = .Cells(.Rows.Count, "A").End(xlUp).Row
      R = IIf(R < StartRow, StartRow, R + 1)
      Set TxtBox = .OLEObjects("SearchText").Object
      Set ChkBox = .OLEObjects("CheckBox1").Object
    End With
 
   'Define the Search Range
    With Worksheets("Sheet1")
      StartRow = 2
      LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
      LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
      Set Rng = .Range(.Cells(StartRow, "A"), .Cells(LastRow, "B"))
    End With
         
     'Compare the Cell Text to the TextBox Text and copy any matches
      For Each Cell In Rng
        I = Cell.Row - StartRow + 1
        Select Case ChkBox.Value
          Case False
            If Cell.Text Like TxtBox.Value Then
              Rng.Rows(I).EntireRow.Copy DstWks.Cells(R, "A")
              R = R + 1
            End If
          Case True
            If LCase(Cell.Text) Like LCase(TxtBox.Value) Then
              Rng.Rows(I).EntireRow.Copy DstWks.Cells(R, "A")
              R = R + 1
            End If
        End Select
      Next Cell
     
End Sub

Sincerely,
Leith Ross

[Becks7]

Thank you very much! It is perfect indeed ... and I owe you a drink