Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
loop and fine & replace | Excel Programming | |||
any function to replace loop | Excel Programming | |||
Msg Box on each Loop to Replace text | Excel Programming | |||
Replace using Do loop | Excel Programming | |||
Replace Loop | Excel Programming |