Glad to be of help; sorry it took a few attempts to understand the objective
;-)


"saturnin02" <saturnin02_at_hotmail.com wrote in message
...
Trevor,
I understand what you are saying about the looping.
It probably would stay withing 500 rows.
We're OK over here.
It does accomplish what is required.
Tx for your help.
I guess I really need to start learning VB....
Best,
S

"Trevor Shuttleworth" wrote in message
...
I somehow thought that might be the next question ...

Sub ReplaceNumber4()
Dim ReplaceWhat
Dim ReplaceWhatPlusOne
Dim i As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
With Range(Range("A1"), Range("A1").End(xlDown))
For i = 99 To 0 Step -1
ReplaceWhat = WorksheetFunction.Text(i, "00")
ReplaceWhatPlusOne = i + 1
If ReplaceWhatPlusOne < 100 Then
ReplaceWhatPlusOne = _
WorksheetFunction.Text(ReplaceWhatPlusOne, "00")
Else: ReplaceWhatPlusOne = "0x0"
End If
.Replace _
What:=ReplaceWhat, _
Replacement:=ReplaceWhatPlusOne, _
LookAt:=xlPart
Next 'i
.Replace _
What:="0x0", _
Replacement:="00", _
LookAt:=xlPart
End With
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub

Seems to do the job but I'm sure there's a better way. I don't like
having
to loop through 100 times but it depends on how many rows of data you
have
as to whether or not it's a problem

Regards

Trevor


"saturnin02" <saturnin02_at_hotmail.com wrote in message
...
Works fine to 99 included.
How do I get it to continue after 99 and go to 00, 01,02 as in year
2000,2001,2002?
Tx a million,
S
"Trevor Shuttleworth" wrote in message
...
S

maybe this will work for you, suitably modified:

Sub ReplaceNumber3()
Dim ReplaceWhat As Integer
Dim i As Integer
For i = 99 To 90 Step -1
ReplaceWhat = i
On Error Resume Next
With Range(Range("A1"), Range("A1").End(xlDown))
.Replace _
What:=ReplaceWhat, _
Replacement:=ReplaceWhat + 1, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End With
On Error GoTo 0
Next 'i
End Sub

Quick and dirty but does the job. You'll need to know the range
though
and
decide what you want to happen to 99.

Regards

Trevor


"saturnin02" <saturnin02_at_hotmail.com wrote in message
...
That wasn't what I wanted.
Here is what I wanted:
A1: a96
B1: b96
C1: c96
etc.

changed to
A1: a97
B1: a97
C1: a97
etc.

But without tying it to "96" or a specific number--just so that
ANY
number
in cells in the range is replaced by same number +1.

S

"Trevor Shuttleworth" wrote in message
...
S

What can I say ? It works for me !

a96
a97
a98
a99
a100
a101
a96
a97
a98
a99
a100
a101
a96
a97
a98
a99
a100
a96
a97
a98
a99
a100
a101




with the second macro and 99 in cell C1:

a96 99
a97
a98
a100
a100
a101
a96
a97
a98
a100
a100
a101
a96
a97
a98
a100
a100
a96
a97
a98
a100
a100
a101



Regards

Trevor


"saturnin02" <saturnin02_at_hotmail.com wrote in message
...
Forgot to say that I get a runtime error 13 mismatch
on second macro you proposed.
S
"Trevor Shuttleworth" wrote in
message
...
S

not sure how you want to provide the number, so one way:

Sub ReplaceNumber()
Dim ReplaceWhat As Integer
ReplaceWhat = 97

With Range(Range("A1"), Range("A1").End(xlDown))
.Replace _
What:=ReplaceWhat, _
Replacement:=ReplaceWhat + 1, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End With
End Sub

Assuming that the value you want to replace is in, say, cell
C1,
you
could
try this as an option:

Sub ReplaceNumber2()
Dim ReplaceWhat As Integer
If Range("C1").Value = "" Then Exit Sub
ReplaceWhat = Range("C1").Value
On Error Resume Next
With Range(Range("A1"), Range("A1").End(xlDown))
.Replace _
What:=ReplaceWhat, _
Replacement:=ReplaceWhat + 1, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End With
On Error GoTo 0
End Sub

This will change 97 to 98, 98 to 99, 99 to 100 and so on.
If
C1
is
blank
it
does nothing ... if it had 0, it would replace all zeroes
with
ones.

Regards

Trevor


"saturnin02" <saturnin02_at_hotmail.com wrote in message
...
Hi, I have recorded a simple Macro as such:
Sub F_R()
'
' F_R Macro
' Macro recorded 4/25/2004 by VSC
'

'
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="97", Replacement:="98",
LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False,
_
ReplaceFormat:=False
End Sub

I would like to modify it so that "98" is replaced by "97
+1"
and
"97"
should be whatever year or numbers are found in cell A1
for
example.
I have a values in cell A1 as such: MNH97,D.
I need the macro to look for the only digits in cell A1
i.e.
97
and
replace
with value (digits) +1 so result is MNH98,D, etc.
(independent
of
the
year
number i.e. if year is 71 then results is 72)

How do I modify above macro or rewrite it?

Tx a million.
S