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!

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!

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!

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

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