Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through a range using a specific start and end
I want to be able to loop through a specific range. The user inputs the begin
cell (A300) and end cell (A350). The user inputs the "old Value" and "new Value". A version of this works if I use Do Until IsEmpty(ActiveCell), but I have to insert an emply row. I want to just enter a being cell and an end cell. Also, can I have the user input more than one variable at a time? See code below: Sub ChangeEquipment() Dim newcellVal Dim oldVal Dim newVal Dim startNum Dim endNum Dim rng As Range Dim myrng As Range startNum = InputBox("Enter start:") endNum = InputBox("Enter end:") Range(startNum).Select oldVal = InputBox("Change from" & vbCrLf & _ "Example: 01- ") newVal = InputBox("Change to" & vbCrLf & _ "Example: 02-") Set rng = Range(startNum & ":" & endNum).Cells For Each myrng In rng 'Do Until IsEmpty(ActiveCell) newcellVal.Row = ActiveCell.Replace(oldVal, newVal, xlPart, xlByRows, False, False, False, False) = False 'ActiveCell.Offset(1, 0).Select Next myrng 'Loop End Sub Thanks, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through a range using a specific start and end
luci,
Here use this Range(rng).Select Selection.Replace What:=oldVal, Replacement:=newVal, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False "luci" wrote: I want to be able to loop through a specific range. The user inputs the begin cell (A300) and end cell (A350). The user inputs the "old Value" and "new Value". A version of this works if I use Do Until IsEmpty(ActiveCell), but I have to insert an emply row. I want to just enter a being cell and an end cell. Also, can I have the user input more than one variable at a time? See code below: Sub ChangeEquipment() Dim newcellVal Dim oldVal Dim newVal Dim startNum Dim endNum Dim rng As Range Dim myrng As Range startNum = InputBox("Enter start:") endNum = InputBox("Enter end:") Range(startNum).Select oldVal = InputBox("Change from" & vbCrLf & _ "Example: 01- ") newVal = InputBox("Change to" & vbCrLf & _ "Example: 02-") Set rng = Range(startNum & ":" & endNum).Cells For Each myrng In rng 'Do Until IsEmpty(ActiveCell) newcellVal.Row = ActiveCell.Replace(oldVal, newVal, xlPart, xlByRows, False, False, False, False) = False 'ActiveCell.Offset(1, 0).Select Next myrng 'Loop End Sub Thanks, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through a range using a specific start and end
This is sort of crude, but should work.
Sub ChangeEquipment() Dim newcellVal Dim oldVal Dim newVal Dim startNum Dim endNum Dim rng As Range Dim myrng As Range startNum = InputBox("Enter start:", "Enter a cell in A1 style without quote marks.") endNum = InputBox("Enter end:", "Enter a cell in A1 style without quote marks.") oldVal = InputBox("Change from" & vbCrLf & "Example: 01- ") newVal = InputBox("Change to" & vbCrLf & "Example: 02-") Set rng = Worksheets(1).Range(startNum & ":" & endNum) rng.Replace What:=oldVal, Replacement:=newVal End Sub "luci" wrote: I want to be able to loop through a specific range. The user inputs the begin cell (A300) and end cell (A350). The user inputs the "old Value" and "new Value". A version of this works if I use Do Until IsEmpty(ActiveCell), but I have to insert an emply row. I want to just enter a being cell and an end cell. Also, can I have the user input more than one variable at a time? See code below: Sub ChangeEquipment() Dim newcellVal Dim oldVal Dim newVal Dim startNum Dim endNum Dim rng As Range Dim myrng As Range startNum = InputBox("Enter start:") endNum = InputBox("Enter end:") Range(startNum).Select oldVal = InputBox("Change from" & vbCrLf & _ "Example: 01- ") newVal = InputBox("Change to" & vbCrLf & _ "Example: 02-") Set rng = Range(startNum & ":" & endNum).Cells For Each myrng In rng 'Do Until IsEmpty(ActiveCell) newcellVal.Row = ActiveCell.Replace(oldVal, newVal, xlPart, xlByRows, False, False, False, False) = False 'ActiveCell.Offset(1, 0).Select Next myrng 'Loop End Sub Thanks, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through a range using a specific start and end
Thanks, that worked. I thought that I would have to use a Loop function.
"JLGWhiz" wrote: This is sort of crude, but should work. Sub ChangeEquipment() Dim newcellVal Dim oldVal Dim newVal Dim startNum Dim endNum Dim rng As Range Dim myrng As Range startNum = InputBox("Enter start:", "Enter a cell in A1 style without quote marks.") endNum = InputBox("Enter end:", "Enter a cell in A1 style without quote marks.") oldVal = InputBox("Change from" & vbCrLf & "Example: 01- ") newVal = InputBox("Change to" & vbCrLf & "Example: 02-") Set rng = Worksheets(1).Range(startNum & ":" & endNum) rng.Replace What:=oldVal, Replacement:=newVal End Sub "luci" wrote: I want to be able to loop through a specific range. The user inputs the begin cell (A300) and end cell (A350). The user inputs the "old Value" and "new Value". A version of this works if I use Do Until IsEmpty(ActiveCell), but I have to insert an emply row. I want to just enter a being cell and an end cell. Also, can I have the user input more than one variable at a time? See code below: Sub ChangeEquipment() Dim newcellVal Dim oldVal Dim newVal Dim startNum Dim endNum Dim rng As Range Dim myrng As Range startNum = InputBox("Enter start:") endNum = InputBox("Enter end:") Range(startNum).Select oldVal = InputBox("Change from" & vbCrLf & _ "Example: 01- ") newVal = InputBox("Change to" & vbCrLf & _ "Example: 02-") Set rng = Range(startNum & ":" & endNum).Cells For Each myrng In rng 'Do Until IsEmpty(ActiveCell) newcellVal.Row = ActiveCell.Replace(oldVal, newVal, xlPart, xlByRows, False, False, False, False) = False 'ActiveCell.Offset(1, 0).Select Next myrng 'Loop End Sub Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
start at a specific point | Charts and Charting in Excel | |||
Start Cells with Specific Value | Excel Discussion (Misc queries) | |||
How to start Excel on a specific Worksheet. | Excel Discussion (Misc queries) | |||
Why does a loop start ? | Excel Programming | |||
From worksheet enter DO-Loop start & end code | Excel Programming |