macro to Find and replace

[bradjedis]

Greetings,

I have on Column A data structure as such:

=>xxxx=>xxxx

This is formatted as general and is not formula just text. I need to replace the "=>" with "=> " same thing with space at the end. Need to structure thru all worksheets.

I have tried the regular Replace dialog, but excel thinks this formula related.


Thoughts? I am open to other characters.. perhaps ">> "

[HansV]

How do you enter =>xxxx=>xxxx in a cell formatted as General? When I try to do that, Excel displays an error message that I'm trying to enter an invalid formula.
But when I format cells as Text and enter such values, Replace will work just fine.
Before:

S1719.png

Replace dialog:

S1720.png

After:

S1721.png

[bradjedis]

Hmmm, I have selected the Column, Formatted to TEXT. I still get the error that Excel thinks this is a formula.

The data came from a different tool as a .CSV, so I am not directly entering the data.

Thoughts?

[bradjedis]

So to get around the issue, I am able to replace the => with >>

This works for me...

[bradjedis]

so what the heck am I doing wrong here.... failing at the With w.Range line

Sub Replace1()
'
' Replace1 Macro
'

'
Dim w As Worksheet
For Each w In Worksheets


With w.Range("A:A").Select
Selection.Replace What:="=>", Replacement:=">> ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End With

Next w


End Sub

[HansV]

You cannot combine With and Select.

Code: Select all

Sub Replace1()
    Dim w As Worksheet
    For Each w In Worksheets
        With w.Range("A:A")
            .Replace What:="=>", Replacement:=">> ", LookAt:=xlPart
        End With
    Next w
End Sub

[bradjedis]

once again., I bow to the master.

[Toranaga]

Hi,

You can use FLASH FILL