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.

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


"wal" wrote:

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

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.


On Nov 29, 11:28*am, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
> 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
>
>
>
> "wal" wrote:
> > 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 -