CODEWITHSUNDEEP
excelvba
Chris2015
Chris2015

Reputation: 1050

Is there a way to use wildcard characters to find an exact match?

I understand the wildcard character can be used to find additional characters on top of a word when filtering. For example, if I am filtering on the word "Sales" I can replace my filter to "Sales*" to find additional characters beyond the first five. Is there a way to use a character to stop and filter on exactly "Sales" i.e. so that results "Sales Operations" would not be included?

I tried this, and i thought it was working, but came to realize it was not.

With Thisworkbook.Sheets("Sheet1")
    If .Range("A2").Value = "Sales" Then
        .Range("A2").Replace "Sales", "Sales?"
    End If
End With

Upvotes: 0

Views: 468

Answers (2)

VBasic2008
VBasic2008

Reputation: 54777

Replace Trouble

You haven't said it explicitly anywhere, but I'm assuming that you used your code:

With Thisworkbook.Sheets("Sheet1")
    If .Range("A2").Value = "Sales" Then
        .Range("A2").Replace "Sales", "Sales?"
    End If
End With

and the result was that it replaced e.g. Sales Operations with Sales? Operations which you didn't want to happen.

The Replace method has 8 arguments while you were using only the obvious first two:

What and Replacement

The reason behind your grief was that due to the last Find or Replace operation you performed, the 3rd argument LookAt was set to xlPart which means that it will find or replace any occurrence in a cell containing your What parameter, "Sales". This is easily fixed by changing your line to:

.Range("A2").Replace "Sales", "Sales?", xlWhole

Now, like LookAt, there are two more important arguments which are saved each time a Replace or Find is performed:

SearchDirection and MatchCase

which are also best set each time a Replace or Find is performed.

You should carefully study those two methods, because they are not as simple (easy) as one might think.

Upvotes: 1

Andy G
Andy G

Reputation: 19367

You do not need to use the wildcard feature. If you record a macro use the Replace feature, and choose Options and tick Match entire cell contents you will see code like this.

Sub Macro2()

    Cells.Find(What:="Sales", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Replace What:="Sales", Replacement:="Foo", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.FindNext(After:=ActiveCell).Activate
End Sub

It is LookAt:=xlWhole that achieves the entire match.

Upvotes: 1

Related Questions