| 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
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 |
|
#5
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 |
|
#6
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 |
|
#7
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 |
|
#8
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! |
|
#9
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! |
|
#10
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 |
| 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 | |||
Hybrid Mode
