How to consider * at the end of string as only a character rather than a wildcard character?

[Amit1]

Dear Friends,
I am having a question regarding macro development with "*" character.
I am trying to develop a macro where i have to sort , count and remove the duplicates for some strings(Each string is existing in a cell).But few strings contains "*" character at the end.
While doing the operations this character is getting considered as a wild card entry which is messing up the counts.
How to declare and write the code in VBA
so that this last "*" character will be considered as only a character rather than wildcard entry.


Some example of the strings in the column are as follows:
aa-bb-cccd
ba-cc-ad
abcird
cca-dc-it*
vva-tt-pq*

Need your help at your earliest convenience.

Best regards
Amit

 

[drsarao]

While doing the operations this character is getting considered as a wild card entry which is messing up the counts.

Post this code.

 

[Amit1]

Hi drsarao,
Could you please paste the code once more.

Thanks,
Amit

 

[Andrew Poulsom]

I think drsarao meant post your code.

To escape a wildcard character precede it with a tilde (~).

 

[Amit1]

Dear Andrew,
Thanks for the suggestion.The primary macro I have generated is as follow:

Sub Test()
'
' Test Macro
' test macro for sorting, count and duplicate removal for string data ending with *
'
' Keyboard Shortcut: Ctrl+p
'
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A8"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:A8")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-1]:R[6]C[-1],RC[-1])"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B8")
    Range("B2:B8").Select
    Range("A2:B8").Select
    ActiveSheet.Range("$A$2:$B$8").RemoveDuplicates Columns:=2, Header:=xlNo
End Sub

This is yielding wrong result.Though I have to cross check for any alternative logic,
could you please let me know how to precede the * with tilde (`) in the code.
I have tried to record a macro with the suggested point but not able to get it.
Waiting for your valuable suggestion.

Best Regards,
Amit

 

[Andrew Poulsom]

You can use this formula:

=COUNTIF(C[-1],SUBSTITUTE(RC[-1],"*","~*"))

In VBA it would be:

ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],SUBSTITUTE(RC[-1],""*"",""~*""))"

 

[Amit1]

Dear Andrew,
Thanks a lot for the suggestion. I will try it out.

Best Regards,
Amit