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!

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


"autoguy" <(E-Mail Removed)> wrote in message
news:de066368-a3c2-40e5-a536-(E-Mail Removed)...
>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!

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


On Oct 19, 7:19*pm, autoguy <br...@justsuspension.com> wrote:
> 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" <(E-Mail Removed)> wrote in message

> 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

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

You may have missed the point but not to worry.

Regards,
Peter T

"joel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> There is nothing wrong with years being 98-02. It is you code that
> isn't very robust. the programmer should write code that will woprk in
> any codition without giving an error.
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
> View this thread:
> http://www.thecodecage.com/forumz/sh...d.php?t=145620
>

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!

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

--
Rick (MVP - Excel)


"autoguy" <(E-Mail Removed)> wrote in message
news:dfd05d61-de68-4f7f-967e-(E-Mail Removed)...
> 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!

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
"autoguy" <(E-Mail Removed)> wrote in message
news:dfd05d61-de68-4f7f-967e-(E-Mail Removed)...
> 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!

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.

--
Rick (MVP - Excel)


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> --
> Rick (MVP - Excel)
>
>
> "autoguy" <(E-Mail Removed)> wrote in message
> news:dfd05d61-de68-4f7f-967e-(E-Mail Removed)...
>> 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!
>