W
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.
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.