| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert range of years to series
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert range of years to series
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 Sub -- 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert range of years to series
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" wrote in message ... 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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert range of years to series
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 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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert range of years to series
What happens if the data is 98-02. See my solution -- 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert range of years to series
"joel" 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert range of years to series
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert range of years to series
Hello,
Just for the fun of it: array-enter =MultiCat(TEXT(ROW(INDIRECT(SUBSTITUTE(A1,"-",":"))),"00")," ") MultiCat you can find he http://sulprobil.com/html/concatenate.html Regards, Bernd |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert range of years to series
You may have missed the point but not to worry.
Regards, Peter T "joel" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert range of years to series
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! |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert range of years to series
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" wrote in message ... 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! |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert range of years to series
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" wrote in message ... 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! |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert range of years to series
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" wrote in message ... 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" wrote in message ... 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! |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert range of years to series
Rick: Worked perfectly! Thanks!
|
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert range of years to series
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 Sub -- 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert Range of Years to Individual list (integers) | Excel Programming | |||
| convert # of years, months and days | Excel Discussion (Misc queries) | |||
| Need to convert years into days | Excel Discussion (Misc queries) | |||
| Convert years to years and days | Excel Discussion (Misc queries) | |||
| how do i calculate CAGR for a series of years of revenues? | Excel Worksheet Functions | |||
Linear Mode
