CODEWITHSUNDEEP
excelvba
duncan297
duncan297

Reputation: 31

Partial string searches with VBA

I am trying to write a macro for searching for and displaying rows of data based on a search term that is a partial string (typically 4 characters of potentially 16), am a complete beginner at this but so far I have the following (with help from Gord):

    Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet
    .Rows.Hidden = False    'unhides all rows
End With
For Each cell In ActiveSheet.Range("C3:C1000")
    If cell.Value <> ActiveSheet.Range("A1").Value Then _
            cell.EntireRow.Hidden = True
    If ActiveSheet.Range("A1").Value = "" Then
        cell.EntireRow.Hidden = False
    End If
Next
Range("A1").Select  'ready for next value input
Application.ScreenUpdating = True
End Sub

How can I define the search term as a partial match not an exact match and loose case senstivity?

TIA

Duncan

The data string has no special characters only 0-9 and A-Z so presumably the Like function will suit the task. If so how would the macro change? Apologies but am total newbie at this. It tried editing: If ActiveSheet.Range("A1").Value = "" Then
to: If ActiveSheet.Range("A1").Value like "*" Then but no result

Upvotes: 0

Views: 2795

Answers (2)

Verzweifler
Verzweifler

Reputation: 940

Since we can't know if you needle contains like-sensitive characters like # or ?, I'd suggest a solution via instr():

if instr(lcase(haystack), lcase(needle)) > 0 then
' Do Stuff

This gives you the starting position of the first occurrence of needle in haystack, or 0 if needle is not found.

Upvotes: 2

Luboš Suk
Luboš Suk

Reputation: 1546

First try to convert both your strings (needle and haystack) to LCASE() and then use LIKE operator. As in example below

if lcase(haystack) like "*" & lcase(needle) & "*" then
'do something

which will works little like regexp. So it will look into string haystack if contains needle. Where * means everything (or nothing). Is this clear?

Upvotes: 2

Related Questions