Create custom wildcard for searches?

[wal]

Excel 2003

I'm using Excel to compile customized lists of words for spelling
exercises. I have several sheets with lists/tables of words. I want
to extract from them words including, for example, the combination "a"
+ consonant + consonant (e.g., the word "back").

Thus far, I've been managing with simple wildcard searches that find
the words on a given sheet and copy-paste them in a column on another
sheet. E.g., search for the string "a??". However, this gives
incorrect strings, like "bake", that I have to delete manually.

The basic code is:

Dim tXt As String
tXt = InputBox("Enter the string you want to find.", " ", , 0, 0)

With Worksheets(sHt).Range("a1:fb66")
Dim xx, firstAddress
Set xx = .Find(What:=tXt, [etc.])
If Not xx Is Nothing Then
firstAddress = xx.Address
Do
xx.Activate
Selection.Copy
Sheets("TempLists").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Activate
Sheets(sHt).Select
Set xx = .FindNext(xx)
Loop While Not xx Is Nothing And xx.Address <> firstAddress
End If
End With

To eliminate words I don't want, I tried creating a custom wildcard
for the consonants, as follows:

Dim cc(1 To 19) As String
cc(1) = "b"
cc(2) = "c"
...
cc(19) = "z"

Dim tXt As String

tXt = "a" & cc & cc [followed by the With...End With code, above]

However, the "type mismatch" error occurs at the line immediately
above, with the & character highlighted. Evidently, even though "a"
is a string and cc is a string, they can't be combined?

Any ideas for doing what I'm trying to do, even if they're totally
different from the code I've tried so far?

Thanks.

 

[Gary''s Student]

There is a reference you can include for Regular expressions. Once included:

http://msdn.microsoft.com/en-us/library/1400241x(VS.85).aspx

 

[wal]

Thanks very much. Although the actual explanations on the pages you
referred to are about as transparent as the Excel VBA Help files, I
was ultimately able to use them to cobble together some Regular
Expressions code that serves my purposes.

There is a reference you can include for Regular expressions.  Once included:

http://msdn.microsoft.com/en-us/library/1400241x(VS.85).aspx
--
Gary''s Student - gsnu200816

Excel 2003

I'm using Excel to compile customized lists of words for spelling
exercises.  I have several sheets with lists/tables of words.  I want
to extract from them words including, for example, the combination "a"
+ consonant + consonant (e.g., the word "back").

Thus far, I've been managing with simplewildcardsearches that find
the words on a given sheet and copy-paste them in a column on another
sheet.  E.g., search for the string "a??".  However, this gives
incorrect strings, like "bake", that I have to delete manually.

The basic code is:

Dim tXt As String
tXt = InputBox("Enter the string you want to find.", " ", , 0, 0)

With Worksheets(sHt).Range("a1:fb66")
    Dim xx, firstAddress
    Set xx = .Find(What:=tXt, [etc.])
    If Not xx Is Nothing Then
        firstAddress = xx.Address
        Do
            xx.Activate
            Selection.Copy
            Sheets("TempLists").Select
            Selection.PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
            ActiveCell.Offset(1, 0).Activate
            Sheets(sHt).Select
            Set xx = .FindNext(xx)
        Loop While Not xx Is Nothing And xx.Address <> firstAddress
    End If
End With

To eliminate words I don't want, I tried creating a customwildcard
for the consonants, as follows:

Dim cc(1 To 19) As String
    cc(1) = "b"
    cc(2) = "c"
    ...
    cc(19) = "z"

Dim tXt As String

tXt = "a" & cc & cc  [followed by the With...End With code, above]

However, the "type mismatch" error occurs at the line immediately
above, with the & character highlighted.  Evidently, even though "a"
is a string and cc is a string, they can't be combined?

Any ideas for doing what I'm trying to do, even if they're totally
different from the code I've tried so far?

Thanks.- Hide quoted text -

- Show quoted text -