Exercise with Incrementing Years
 

Hi all,

I'm trying to write code that would pick up the existence of a year within a
selected cell and increment it by one, and I've been running into quite a few
snags.
For example, the goal is that the procedure should pick up the number 2007
in the following cells and turn it into 2008:
Revenue 2007 Plans
2007
Cost Center 20079 in Year 2007

I've managed to make it work for the first 2 examples, but I'm frankly out
of ideas for the instance where the cell contains a number that's similar to
2007. Any suggestions are greatly appreciated.

--------
Thanks,
Anony

Exercise with Incrementing Years
 

kind of ugly but it seemed to work...

Public Sub Macro1()
Dim c As Range
Dim i As Integer
Dim r As String

For Each c In Selection
For i = 1 To Len(c)
If Mid(c, i, 1) = "2" Then
If Mid(c, i + 3, 1) = "7" Then
If Mid(c, i + 4, 1) = "" Or Mid(c, i + 4, 1) = " " Then
If Not i + 3 = c Then
c = Mid(c, 1, i + 2) & Replace(c, "7", "8", i + 3)
Else
c = Mid(c, 1, i + 2) & Replace(c, "7", "8", i + 3) & Right(c, Len(c) -
(i + 3))
End If

End If
End If
End If

Next
Next

End Sub

"Anony" wrote:

Hi all,

I'm trying to write code that would pick up the existence of a year within a
selected cell and increment it by one, and I've been running into quite a few
snags.
For example, the goal is that the procedure should pick up the number 2007
in the following cells and turn it into 2008:
Revenue 2007 Plans
2007
Cost Center 20079 in Year 2007

I've managed to make it work for the first 2 examples, but I'm frankly out
of ideas for the instance where the cell contains a number that's similar to
2007. Any suggestions are greatly appreciated.

--------
Thanks,
Anony

Exercise with Incrementing Years
 

I would use the existence of leading and trailing spaces around "2007" to
identify it as a year. This also allows us to change all occurences of " 2007
" in a string.

Using your sample data in cell A1 to A3 try:

Public Sub xyz()

Dim i As Integer
Dim j As Integer
Dim ourString As String
Dim pos As Integer
Dim frontSpace As Boolean
Dim trailingSpace As Boolean

For i = 1 To 3
ourString = Trim(Range("A" & i))
If ourString = "2007" Then 'special case for just "2007"
Range("A" & i) = "2008"
Else
'check for spaces around any occurence of "2007"
j = 1
Do While InStr(j, ourString, "2007") 0
pos = InStr(j, ourString, "2007") 'set position
j = pos + 1

frontSpace = False 'test for fronting space
If pos = 1 Then ' 2007 is at start of cell, default to True
frontSpace = True
Else
If Mid(ourString, (pos - 1), 1) = " " Then frontSpace = True
End If

trailingSpace = False 'test for trailing space
If pos = Len(ourString) - 3 Then ' 2007 is at end of cell,
default to True
trailingSpace = True
Else
If Mid(ourString, (pos + 4), 1) = " " Then trailingSpace =
True
End If

If frontSpace And trailingSpace Then 'replace 2007 with 2008
ourString = _
Left(ourString, (pos - 1)) _
& "2008" _
& Right(ourString, Len(ourString) - pos - 3)
End If
Loop
End If
Range("A" & i) = ourString
Next i

End Sub
--
Les Torchia-Wells


"Anony" wrote:

Hi all,

I'm trying to write code that would pick up the existence of a year within a
selected cell and increment it by one, and I've been running into quite a few
snags.
For example, the goal is that the procedure should pick up the number 2007
in the following cells and turn it into 2008:
Revenue 2007 Plans
2007
Cost Center 20079 in Year 2007

I've managed to make it work for the first 2 examples, but I'm frankly out
of ideas for the instance where the cell contains a number that's similar to
2007. Any suggestions are greatly appreciated.

--------
Thanks,
Anony

Exercise with Incrementing Years
 

Thank you all for your help. But I guess I probably should have been more
specific in my question. To turn 2007 into 2008 was only an example, and I
was hoping to increment all year values by 1. I was able to resolve the
issue with the following code (which I find to be pretty hopeful for when
you're updating a sheet with a lot of year values):

Sub IncrementYear()
Dim Yr
Dim InputYr As String
Dim i As Integer
Dim LastDigit As Variant
Dim ThisRange As Range, SelectCell As Range

InputYr = "20??"

For Each SelectCell In Selection
Set Yr = SelectCell.Find(InputYr, LookIn:=xlValues)
If Not Yr Is Nothing Then
i = 0
Do
i = i + 1
Loop Until i = Len(Yr) Or (Mid(Yr, i, 4) Like InputYr)

LastDigit = Mid(Yr, i, 4)
LastDigit = Val(LastDigit) + 1
Mid(Yr, i, 4) = LastDigit
SelectCell = Yr
End If
Next SelectCell

End Sub

--------
Cheers,
Anony


"Les" wrote:

I would use the existence of leading and trailing spaces around "2007" to
identify it as a year. This also allows us to change all occurences of " 2007
" in a string.

Using your sample data in cell A1 to A3 try:

Public Sub xyz()

Dim i As Integer
Dim j As Integer
Dim ourString As String
Dim pos As Integer
Dim frontSpace As Boolean
Dim trailingSpace As Boolean

For i = 1 To 3
ourString = Trim(Range("A" & i))
If ourString = "2007" Then 'special case for just "2007"
Range("A" & i) = "2008"
Else
'check for spaces around any occurence of "2007"
j = 1
Do While InStr(j, ourString, "2007") 0
pos = InStr(j, ourString, "2007") 'set position
j = pos + 1

frontSpace = False 'test for fronting space
If pos = 1 Then ' 2007 is at start of cell, default to True
frontSpace = True
Else
If Mid(ourString, (pos - 1), 1) = " " Then frontSpace = True
End If

trailingSpace = False 'test for trailing space
If pos = Len(ourString) - 3 Then ' 2007 is at end of cell,
default to True
trailingSpace = True
Else
If Mid(ourString, (pos + 4), 1) = " " Then trailingSpace =
True
End If

If frontSpace And trailingSpace Then 'replace 2007 with 2008
ourString = _
Left(ourString, (pos - 1)) _
& "2008" _
& Right(ourString, Len(ourString) - pos - 3)
End If
Loop
End If
Range("A" & i) = ourString
Next i

End Sub
--
Les Torchia-Wells


"Anony" wrote:

Hi all,

I'm trying to write code that would pick up the existence of a year within a
selected cell and increment it by one, and I've been running into quite a few
snags.
For example, the goal is that the procedure should pick up the number 2007
in the following cells and turn it into 2008:
Revenue 2007 Plans
2007
Cost Center 20079 in Year 2007

I've managed to make it work for the first 2 examples, but I'm frankly out
of ideas for the instance where the cell contains a number that's similar to
2007. Any suggestions are greatly appreciated.

--------
Thanks,
Anony