asterisks in replace macro

[janelange]

Hi all,

I am trying to write a macro in excel that replaces every asterisk (*) in a column with a 1.

I run into a problem because VBA thinks that I am referring to the wild card string instead of an asterisk.
It replaces everything that I have selected with 1, rather than only the asterisks.

Here is what the code looks like now:

Columns("J:J").Select
Selection.Replace What:="*", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Any suggestions?

Thanks,
Jane

 

[cLFlaVA]

Rather than use the Find / Replace mechanism, you could use straight VB code --

Dim y As Long
For y = 1 To Range("J:J").Rows.Count Step 1
    If InStr(Range("J" & CStr(y)).Value, "*") Then
        Range("J" & CStr(y)).Value = Replace(Range("J" & CStr(y)).Value, "*", "1")
    End If
Next y

worked for me

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[xlbo]

Please search the archives before asking a question. The answer is to use a ~ (tilde) before the asterisk to indicate it is a literal * rather than a wildcard.

a search on:
asterisk wildcard find

would've brought up several threads on this

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Chance1234]

try chr(42) instead of "*"

Chance,

Filmmaker, gentlemen and buccaneer

http://WWW.5YLAC.S5.COM

,

http://WWW.HOWTOBESPOOKY.CO.UK,

http://WWW.HENRYV.CO.UK

 

[xlbo]

cLFlaVA - that would take a looooooong time on 30000 rows - even if there were not many asterisks - the FIND method is much quicker as it only works on what is there.

Chance - nope sorry mate - doesn't work

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[cLFlaVA]

xlbo-

You are very correct. I wasn't aware of the tilde functionality, and was just trying to give an alternate method.



*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[janelange]

thanks for the help, folks!

Sorry for the redundant question--

JL