Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Range of Years to Individual list (integers)
For example, I need to convert this:
1995-2000 (1 column, 1 row) to this: 1995 1996 1997 1998 1999 2000 (1 column, 6 rows) If anyone can help, it will be greatly appreciated! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Range of Years to Individual list (integers)
Hi
Look at this: Sub aaa() Dim Del As Long Dim StartYear As Long Dim EndYear As Long Dim y As Long Dim off As Long Del = WorksheetFunction.Find("-", Range("A1").Value) StartYear = Left(Range("A1").Value, Del - 1) EndYear = Mid(Range("A1").Value, Del + 1) For y = StartYear To EndYear Range("A1").Offset(off, 0) = y off = off + 1 Next End Sub Regards, Per On 6 Maj, 23:19, wrote: For example, I need to convert this: 1995-2000 (1 column, 1 row) to this: 1995 1996 1997 1998 1999 2000 (1 column, 6 rows) If anyone can help, it will be greatly appreciated! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Range of Years to Individual list (integers)
You could do something like this:
Sub SplitYears() Dim i As Long Dim r As Long Dim arrYears arrYears = Split(Cells(1), "-") For i = arrYears(0) To arrYears(UBound(arrYears)) r = r + 1 Cells(r, 1) = i Next i End Sub RBS wrote in message ... For example, I need to convert this: 1995-2000 (1 column, 1 row) to this: 1995 1996 1997 1998 1999 2000 (1 column, 6 rows) If anyone can help, it will be greatly appreciated! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Range of Years to Individual list (integers)
Thanks for the quick responses, everyone! Let me make my project a
little more clear and see what you think. The year values are part of a group of data, and the other data needs to be copied intact along with the expanded year values. For example, convert this: (3 columns, 2 rows) Acura EL 2002-2005 Acura Integra 1990-1993 To this: (3 columns, 7 rows) Acura EL 2002 Acura EL 2003 Acura EL 2004 Acura EL 2005 Acura Integra 1990 Acura Integra 1991 Acura Integra 1992 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Range of Years to Individual list (integers)
Hi
You never told where you want output to be placed, so I placed it in Sheet2, assuming input data is in Sheet1. Sub aaa() Dim Del As Long Dim StartYear As Long Dim EndYear As Long Dim y As Long Dim off As Long Dim OutputSh As Worksheet Set OutputSh = Worksheets("Sheet2") FirstRow = 2 ' Headings in Row 1 LastRow = Range("A" & Rows.Count).End(xlUp).Row For r = FirstRow To LastRow Del = WorksheetFunction.Find("-", Range("C" & r).Value) StartYear = Left(Range("C" & r).Value, Del - 1) EndYear = Mid(Range("C" & r).Value, Del + 1) For y = StartYear To EndYear OutputSh.Range("C2").Offset(off, 0) = y OutputSh.Range("A2").Offset(off, 0) = Range("A" & r).Value OutputSh.Range("B2").Offset(off, 0) = Range("B" & r).Value off = off + 1 Next Next End Sub Regards, Per On 7 Maj, 00:13, wrote: Thanks for the quick responses, everyone! *Let me make my project a little more clear and see what you think. *The year values are part of a group of data, and the other data needs to be copied intact along with the expanded year values. *For example, convert this: (3 columns, 2 rows) Acura EL 2002-2005 Acura Integra 1990-1993 To this: (3 columns, 7 rows) Acura EL 2002 Acura EL 2003 Acura EL 2004 Acura EL 2005 Acura Integra 1990 Acura Integra 1991 Acura Integra 1992 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert a range of cells into individual worksheets? | Excel Discussion (Misc queries) | |||
Multiple selection (range or individual) capability in a drop down list | Excel Programming | |||
Convert Data Range in to individual values | Excel Discussion (Misc queries) | |||
Convert years to years and days | Excel Discussion (Misc queries) | |||
A list of Consecutive Integers, can I search for missing integers | Excel Worksheet Functions |