View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
wal wal is offline
external usenet poster
 
Posts: 19
Default Create custom wildcard for searches?

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.