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 ">> "
macro to Find and replace
-
- 4StarLounger
- Posts: 547
- Joined: 30 Mar 2010, 18:49
- Location: United States
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: macro to Find and replace
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:
Replace dialog:
After:
But when I format cells as Text and enter such values, Replace will work just fine.
Before:
Replace dialog:
After:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 547
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: macro to Find and replace
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?
The data came from a different tool as a .CSV, so I am not directly entering the data.
Thoughts?
-
- 4StarLounger
- Posts: 547
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: macro to Find and replace
So to get around the issue, I am able to replace the => with >>
This works for me...
This works for me...
-
- 4StarLounger
- Posts: 547
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: macro to Find and replace
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
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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: macro to Find and replace
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
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 547
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: macro to Find and replace
once again., I bow to the master.
-
- 3StarLounger
- Posts: 256
- Joined: 15 Aug 2016, 11:23
Re: macro to Find and replace
Hi,
You can use FLASH FILL
You can use FLASH FILL