convert range of years to series

[autoguy]

I have the following range of years in column A
02-08
91-99
92-96
01-03

What I need (in column B) is
02 03 04 05 06 07 08
91 92 93 94 95 96 97 98 99
92 93 94 95 96
01 02 03

Thanks for your help!

 

[joel]

Sub expandyears()
Dim Outputstr As String
RowCount = 1
Do While Range("A" & RowCount) <> ""
Years = Range("A" & RowCount)
FirstYear = Val(Trim(Years))
LastYear = Val(Trim(Mid(Years, InStr(Years, "-") + 1)))
Outputstr = ""
YearCount = FirstYear Mod 100
Do While YearCount <> LastYear
If Outputstr = "" Then
Outputstr = Format(YearCount, "#00")
Else
Outputstr = Outputstr & Format(YearCount, " #00")
End If
YearCount = (YearCount + 1) Mod 100
Loop
Range("B" & RowCount).NumberFormat = "@"
Range("B" & RowCount) = Outputstr
RowCount = RowCount + 1
Loop

End Su

 

[Peter T]

Sub test3()
Dim i As Long
Dim first As Long, last As Long
dim s as String
Dim cel As Range, rng As Range

Set rng = Range("A1:A4")

For Each cel In rng
first = Val(Left$(cel.Value, 2))
last = Val(Right$(cel.Value, 2))
s = ""
For i = first To last
s = s & Right$("0" & i, 2)
If i < last Then
s = s & " "
End If
Next
cel.Offset(, 1) = s
Next

End Sub

I assum A1:A4 is already formatted as text to accept strings like 02-08 with
converting to a date or evalutaing the subtration.

Regards,
Peter T

 

[muddan madhu]

try this UDF

Function evalua(inputv)
R1 = Left(inputv, 2)
R2 = Right(inputv, 2)
R3 = R2 - R1
For i = 0 To R3
REsult = REsult & " " & Format(R1 + i, "00")
Next i
evalua = REsult
End Function

 

[joel]

What happens if the data is 98-02. See my solutio

 

[Peter T]

What happens if the data is 98-02.

Running my macro you'd immediately realise the input data was incorrect, ie
not a range of years in a single decade

Regards,
Peter T

 

[joel]

There is nothing wrong with years being 98-02. It is you code tha
isn't very robust. the programmer should write code that will woprk i
any codition without giving an error

 

[Bernd P]

Hello,

Just for the fun of it: array-enter
=MultiCat(TEXT(ROW(INDIRECT(SUBSTITUTE(A1,"-",":"))),"00")," ")

MultiCat you can find here:
http://sulprobil.com/html/concatenate.html

Regards,
Bernd

 

[Peter T]

You may have missed the point but not to worry.

Regards,
Peter T

 

[autoguy]

Running Joel's code, this is what I get
96-99 96 97 98
89-91 89 90
91-96 91 92 93 94 95
91-96 91 92 93 94 95
87-92 87 88 89 90 91
03-07 03 04 05 06
85-95 85 86 87 88 89 90 91 92 93 94
02-06 02 03 04 05
99-00 99
96-05 96 97 98 99 00 01 02 03 04
90-95 90 91 92 93 94
96-05 96 97 98 99 00 01 02 03 04
90-95 90 91 92 93 94
02-06 02 03 04 05
02-06 02 03 04 05
02-06 02 03 04 05
02-06 02 03 04 05
02-06 02 03 04 05
92-94 92 93
85-91 85 86 87 88 89 90
04-05 04
99-03 99 00 01 02
95-98 95 96 97
83-94 83 84 85 86 87 88 89 90 91 92 93
95-05 95 96 97 98 99 00 01 02 03 04
83-94 83 84 85 86 87 88 89 90 91 92 93

It's cutting the last number out ... any idea how to rectify that?
THANKS!

 

[Rick Rothstein]

While this isn't joel's version, here is a function I developed which will
do what you want...

Sub FillAcross()
Dim X As Long, Z As Long, LastRow As Long, S As String, V As Variant
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
If Cells(X, "A").Value Like "##-##" Then
V = Split(Cells(X, "A").Value, "-")
S = ""
For Z = V(0) To V(1) - 100 * (V(1) < V(0))
S = S & " " & Format(Format(Z, "00"), "!@@")
Next
Cells(X, "B") = Trim(S)
End If
Next
End Sub

 

[Peter T]

another one -

Sub test4()
Dim i As Long
Dim first As Long, last As Long
Dim cel As Range, rng As Range

Set rng = Range("A1:A26")

For Each cel In rng
first = Val(Left$(cel.Value, 2)) + 1900
If first < 1950 Then first = first + 100

last = Val(Right$(cel.Value, 2)) + 1900
If last < 1950 Then last = last + 100
s = ""
For i = first To last
s = s & Right$("0" & i, 2)
If i < last Then
s = s & " "
End If
Next
cel.Offset(, 1) = s
Next

End Sub

change the "1950" to suit, ie 50 = 1950 and 49 = 2049

Regards,
Peter T

 

[Rick Rothstein]

Of course the code I posted is not a "function"... it is a "macro". By the
way, I didn't mention it, but the code will properly ignore cell values that
are not constructed as digit-digit-dash-digit-digit, so you can have text
and blank cells within the range and that will not "screw" things up any.

 

[autoguy]

Rick: Worked perfectly! Thanks!

 

[joel]

try this

Sub expandyears()
Dim Outputstr As String
RowCount = 1
Do While Range("A" & RowCount) <> ""
Years = Range("A" & RowCount)
FirstYear = Val(Trim(Years))
LastYear = Val(Trim(Mid(Years, InStr(Years, "-") + 1)))
Outputstr = ""
YearCount = FirstYear Mod 100
Do While (1)
If Outputstr = "" Then
Outputstr = Format(YearCount, "#00")
Else
Outputstr = Outputstr & Format(YearCount, " #00")
End If
If YearCount = LastYear Then
Exit Do
Else
YearCount = (YearCount + 1) Mod 100
End If
Loop
Range("B" & RowCount).NumberFormat = "@"
Range("B" & RowCount) = Outputstr
RowCount = RowCount + 1
Loop

End Su