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