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

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

Trevor,
Tx for your reply and advice.
Does not work.
Input Range is in A1 going down to end in A40 orA150, as the case may be.
I guess that is the Range("A1").End(xlDown)
Then in same range, replace the yeear numbers.
So I guess first select the range and then in that range do the replace....
Any ideas?
Tx,
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

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

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

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

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