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.

Create custom wildcard for searches?
 

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

http://msdn.microsoft.com/en-us/libr...1x(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.

Create custom wildcard for searches?
 

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
wrote:
There is a reference you can include for Regular expressions. *Once included:

http://msdn.microsoft.com/en-us/libr...1x(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 -