Simple Macro

[saturnin02]

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 Shuttleworth]

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]

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

 

[saturnin02]

Forgot to say that I get a runtime error 13 mismatch
on second macro you proposed.
S

 

[Trevor Shuttleworth]

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]

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]

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]

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]

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]

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]

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

 

[saturnin02]

Trevor,
Darn it--I forgot to mention the following:
We're almost there....

I have in cells
A1 = mm97z.60
B1 = mm97z.15
C1 = mm97z.5
etc...

I need to create a macro that will look for the first digits it encouters in
the cells and replace then with same digits but +1 (e.g. the 97 in cell
A1 -->mm97z.60 would turn into 98 with result mm98z.60.

After reaching 99, it needs to continue not as 100, 101, etc., but as 00,
01, etc.

This needs to be done for a range of cells that are always in Column A but
with various possible lengths which I usually select manually with
Control\End Down Arrow.

The problem with the 4th macro is that it replaces or adds 1 to ALL numbers
when I need it to do it ONLY on the "97" position I mean the digits located
at the 3rd and 4th position only! Everything after the "." needs to stay as
is.

I know I am pushing my luck now and trying your patience no doubt.....
S

 

[Trevor Shuttleworth]

"I know I am pushing my luck now and trying your patience no doubt....."

In one ! Where I come from that's called moving the goal posts. It's
called other things too ...

 

[Trevor Shuttleworth]

suggest you use text to columns delimited by a comma or period or whatever,
run the macro on the first column and then recombine the columns. You can
record each stage and then combine the recorded macros.

Recording your own macros IS the best way of learning,
supplemented/complemented by these groups. If you have a problem you could
start a new thread with the revised problem and progress to date

Regards

Trevor

 

[saturnin02]

I'll figure it out.
Tx for your help Trevor.
S

suggest you use text to columns delimited by a comma or period or whatever,
run the macro on the first column and then recombine the columns. You can
record each stage and then combine the recorded macros.

Recording your own macros IS the best way of learning,
supplemented/complemented by these groups. If you have a problem you could
start a new thread with the revised problem and progress to date

Regards

Trevor


encouters