Excel VBA wildcard search

by

You can use Like operator (case-sensitive):

Private Sub search_Click()
    For firstloop = 3 To 10
        If Range("G" & firstloop).Text Like name.Text & "*" Then
            MsgBox "Found!"
            Exit Sub
        Else
            MsgBox "NOT FOUND"
        End If
    Next
End Sub

for case-insensitive search use:

If UCase(Range("G" & firstloop).Text) Like UCase(name.Text) & "*" Then

Also if you want to determine whether cell contains text (not only starts with text), you can use (case-sensitive):

If InStr(1, Range("G" & firstloop).Text, name.Text) > 0 Then

or (case-insensitive)

If InStr(1, Range("G" & firstloop).Text, name.Text, vbTextCompare) > 0 Then

UPD:

If the point only to show msgbox, then I’d suggest to use Application.Match:

Private Sub search_Click()
    If Not IsError(Application.Match("abc" & "*", Range("G3:G10"), 0)) Then
        MsgBox "Found!"
    Else
        MsgBox "NOT FOUND"
    End If
End Sub

More Related Contents:

  1. VBA – How to grab the cell below if a cell has text?
  2. Unprotect VBProject from VB code
  3. Getting ScriptControl to work with Excel 2010 x64
  4. Pop up the Excel Statusbar?
  5. filter out multiple criteria using excel vba
  6. Get list of Excel files in a folder using VBA [duplicate]
  7. Assign event handlers to controls on user form created dynamically in VBA
  8. When one should use Set [e.g for SpecialCells return value]?
  9. Should I turn .CutCopyMode back on before exiting my sub procedure?
  10. Determine if cell contains data validation
  11. VBA: How to delete filtered rows in Excel?
  12. Sheet protection: UserInterFaceOnly gone
  13. VBScript – How to make program wait until process has finished?
  14. Round function in Excel, worksheet function vs VBA
  15. How to highlight a cell using the hex color value within the cell?
  16. Removing special characters VBA Excel
  17. Excel VBA Userform – Execute Sub when something changes
  18. Cannot VBA write data to cells in Excel 2007/2010 within a function
  19. Open an Excel file from SharePoint site
  20. Excel: the Incredible Shrinking and Expanding Controls [closed]
  21. simple vba code gives me run time error 91 object variable or with block not set
  22. VBA Function argument list select
  23. VBA recognizing workbook by partial name
  24. Reading Web Pages using Excel VBA
  25. How do I loop an excel 2010 table by using his name & column reference?
  26. Excel VBA Project has generated multiple Workbook objects
  27. Copy an entire worksheet to a new worksheet in Excel 2010
  28. Get all dates between 2 dates in vba
  29. Remove double quotation from write statement in vba
  30. How to improve the speed of VBA macro code?

Leave a Comment