convert range of years to series

A

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!
 
J

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
 
P

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
 
M

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
 
P

Peter T

joel said:
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
 
J

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
 
A

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!
 
R

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
 
P

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
 
R

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.
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top