How can I display multinominal results

Microsoft Office excel 2007 home & stude...
December 7, 2010 at 08:13:07
Specs: Windows 7
Hello,
I've been pulling my hair trying to figure out how to display the difference between two cells into one cell in multinomal result.

For example:

From Year..............To Year.............. Equals
1997...................... 2001...................1997, 1998, 1999, 2000, 2001

Does excel have a formula that can do this?


See More: How can I display multinominal results

Report •


#1
December 7, 2010 at 10:55:42
No, Excel does not have a function to do that. I can't even imagine why they would.

You are going to need a macro.

This might work:

With the early year (e.g. 1997) in A1 and the later year (e.g. 2001) in A2, this will put your desired result in A3:

Option Explicit
Sub EveryYear()
Dim yearDif, nxtYear As Integer
Dim tmpString As String
'Subtract A1 from A2
 yearDif = Sheets(1).Range("A2") - Sheets(1).Range("A1")
'Set Temporary String = A1
 tmpString = Sheets(1).Range("A1")
'Build String by looping
   For nxtYear = 1 To yearDif
     tmpString = tmpString & ", " & Sheets(1).Range("A1") + nxtYear
   Next
'Place Complete String in A3
  Sheets(1).Range("A3") = tmpString
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 7, 2010 at 12:01:08
Hey, that worked! Thanks.
One thing though, how can I make it happen on 3000+ line items simultaneously?

I tried to drag it down, but the macro just repeats the same numbers all the way down.


Report •

#3
December 7, 2010 at 13:59:49
I'm not sure what you mean by "3000+ line items simultaneously".

And I don't know what you "dragged down".

You are going to have to give us some more information about how your sheet is laid out.

If you are going to post any more data like you did in your OP, please read the How To referenced in the following line.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions


Ask Question