Can this be done?

[michael.beckinsale]

Hi all,

The snippet of code below is what l am using to replace "deposit "
with "deposit-". Where the spaces in "deposit " can be anything from
1 space to 6 spaces. Can l use a wildcard character such as * to do
this? Any suggestions gratefully received. For information the range
the replace is to work on covers 1 column with a dynamic number of rows
usually no more than 1000.

With ActiveSheet
On Error Resume Next
.Replace What:="DEPOSIT ", Replacement:="DEPOSIT-",
lookat:=xlPart, searchorder _

:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
On Error GoTo 0
End With

Regards

Michael Beckinsale

 

[Satish]

Michael,

It seems you want to replace all words where you have "deposit " (1
or more spaces) with "deposit-".

If that is the case, you can simply use the RTrim function to convert
all "deposit " (1 or more spaces) to "deposit" and then use your
replace code.

HTH,

-Satish

 

[Gary Keramidas]

post a typical cell contents. depending on what's in the cell, maybe trim?

 

[michael.beckinsale]

Gary / Satish,

Thanks for your input but with a little playing about using the macro
recorder got the code to use wildcards to find / replace as
appropriate. I think the problem was caused by a combination of the 'On
Error Resume Next' statement, the defined range to carry out the
..Replace action (ie say A1:A1000), and then searching by Rows not
Columns.

The succesful code is pasted below.

On Error Resume Next
.Replace What:="DEPOSIT*????", Replacement:="DEPOSIT-",
LookAt:=xlPart, SearchOrder _

:=xlByColumns, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
On Error GoTo 0


Regards

Michael Beckinsale