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,

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,

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,

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,