Loop through a range using a specific start and end

L

luci

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,
 
J

JRForm

luci,

Here use this

Range(rng).Select
Selection.Replace What:=oldVal, Replacement:=newVal, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
 
L

luci

I tryed this code, but I received an error:
********************************
* Microsoft Visual Basic
*
* Run-time error '1004':
*
*Method 'Range' of object '_Global' failed.
********************************
I would like to reiterate that my goal is to be able to run a loop that I
can enter a specific beginning and a specific end.
The code I had originally works with a Do Until IsEmpty(ActiveCell) loop.

Thank you.
 
J

JLGWhiz

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top