replace text with a loop
 

If I wanted to replace text "A3" with "A4" I would do this

Cells.Replace What:="A3", _
Replacement:="A4", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

How could I put this in a loop to also replace also "B3" with "B4", "C3"
with "C4", all the way to "Z3" with "Z4"?

replace text with a loop
 

Give this a try...

Dim Index As Long
For Index = Asc("A") To Asc("Z")
Cells.Replace What:=Chr(Index) & "3", _
Replacement:=Chr(Index) & "4", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Next

--
Rick (MVP - Excel)


"Scooter" wrote in message
...
If I wanted to replace text "A3" with "A4" I would do this

Cells.Replace What:="A3", _
Replacement:="A4", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

How could I put this in a loop to also replace also "B3" with "B4", "C3"
with "C4", all the way to "Z3" with "Z4"?

replace text with a loop
 

Option Explicit
Sub testme()

Dim lCtr As Long

With ActiveSheet
For lCtr = Asc("A") To Asc("Z")
.Cells.Replace What:=Chr(lCtr) & "3", _
Replacement:=Chr(lCtr) & "4", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Next lCtr
End With

End Sub



Scooter wrote:

If I wanted to replace text "A3" with "A4" I would do this

Cells.Replace What:="A3", _
Replacement:="A4", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

How could I put this in a loop to also replace also "B3" with "B4", "C3"
with "C4", all the way to "Z3" with "Z4"?

--

Dave Peterson

replace text with a loop
 

Thanks that worked like a charm. It brought to light another question.
How can I go past Z to say AA, AB, AC...? (these letters represent the Excel
column letters).

"Rick Rothstein" wrote:

Give this a try...

Dim Index As Long
For Index = Asc("A") To Asc("Z")
Cells.Replace What:=Chr(Index) & "3", _
Replacement:=Chr(Index) & "4", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Next

--
Rick (MVP - Excel)


"Scooter" wrote in message
...
If I wanted to replace text "A3" with "A4" I would do this

Cells.Replace What:="A3", _
Replacement:="A4", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

How could I put this in a loop to also replace also "B3" with "B4", "C3"
with "C4", all the way to "Z3" with "Z4"?

replace text with a loop
 

Thanks that worked like a charm. It brought to light another question.
How can I go past Z to say AA, AB, AC...? (these letters represent the Excel
column letters).


"Dave Peterson" wrote:

Option Explicit
Sub testme()

Dim lCtr As Long

With ActiveSheet
For lCtr = Asc("A") To Asc("Z")
.Cells.Replace What:=Chr(lCtr) & "3", _
Replacement:=Chr(lCtr) & "4", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Next lCtr
End With

End Sub



Scooter wrote:

If I wanted to replace text "A3" with "A4" I would do this

Cells.Replace What:="A3", _
Replacement:="A4", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

How could I put this in a loop to also replace also "B3" with "B4", "C3"
with "C4", all the way to "Z3" with "Z4"?

--

Dave Peterson

replace text with a loop
 

Maybe you can use something like:

Option Explicit
Sub testme()

Dim lCtr As Long
Dim myStr As String

With ActiveSheet
For lCtr = .Range("a1").Column To .Range("IV1").Column
'.address(0,0) will be like A1 or BA1 or IV1
myStr = .Cells(1, lCtr).Address(0, 0)
'remove the 1, to get A or BA or IV
myStr = Left(myStr, Len(myStr) - 1)
.Cells.Replace What:=myStr & "3", _
Replacement:=myStr & "4", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Next lCtr
End With

End Sub



Scooter wrote:

Thanks that worked like a charm. It brought to light another question.
How can I go past Z to say AA, AB, AC...? (these letters represent the Excel
column letters).

"Dave Peterson" wrote:

Option Explicit
Sub testme()

Dim lCtr As Long

With ActiveSheet
For lCtr = Asc("A") To Asc("Z")
.Cells.Replace What:=Chr(lCtr) & "3", _
Replacement:=Chr(lCtr) & "4", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Next lCtr
End With

End Sub



Scooter wrote:

If I wanted to replace text "A3" with "A4" I would do this

Cells.Replace What:="A3", _
Replacement:="A4", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

How could I put this in a loop to also replace also "B3" with "B4", "C3"
with "C4", all the way to "Z3" with "Z4"?

--

Dave Peterson


--

Dave Peterson

replace text with a loop
 

Since you are doing a xlPart search, the posted code will handle those
situations automatically... if you have C3, AC3 or even HC3 in a cell, the
C3 to C4 replacement will catch all the occurrences automatically

--
Rick (MVP - Excel)


"Scooter" wrote in message
...
Thanks that worked like a charm. It brought to light another question.
How can I go past Z to say AA, AB, AC...? (these letters represent the
Excel
column letters).

"Rick Rothstein" wrote:

Give this a try...

Dim Index As Long
For Index = Asc("A") To Asc("Z")
Cells.Replace What:=Chr(Index) & "3", _
Replacement:=Chr(Index) & "4", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Next

--
Rick (MVP - Excel)


"Scooter" wrote in message
...
If I wanted to replace text "A3" with "A4" I would do this

Cells.Replace What:="A3", _
Replacement:="A4", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

How could I put this in a loop to also replace also "B3" with "B4",
"C3"
with "C4", all the way to "Z3" with "Z4"?