Can I create a list this way?

Maven · Aug 21, 2006

Is there a way I can use a number in a cell to fill in that number o
cells with a given value? For example, is there a way that I coul
enter this kind of information:

Number blue 3
Number red 2

And get this returned to me:

Blue
Blue
Blue
Red
Red

Any suggestions would be appreciated

MyVeryOwnSelf · Aug 21, 2006

Is there a way I can use a number in a cell to fill in that number of

cells with a given value? For example, is there a way that I could
enter this kind of information:

Number blue 3
Number red 2

And get this returned to me:

Blue
Blue
Blue
Red
Red

Maybe the CSV file below would help. The independent variable numbers are
in column D.

-------------------- cut here --------------------
"=VLOOKUP(ROW()-1,B

,2,TRUE)",0,blue,3
"=VLOOKUP(ROW()-1,B

,2,TRUE)",=B1+D1,red,2
"=VLOOKUP(ROW()-1,B

,2,TRUE)",=B2+D2,green,6
"=VLOOKUP(ROW()-1,B

,2,TRUE)",=B3+D3,orange,5
"=VLOOKUP(ROW()-1,B

,2,TRUE)",=B4+D4,"=""""",
"=VLOOKUP(ROW()-1,B

,2,TRUE)",,,
"=VLOOKUP(ROW()-1,B

,2,TRUE)"
"=VLOOKUP(ROW()-1,B

,2,TRUE)"

scottfoxall · Aug 21, 2006

If i understand correctly?

You have a drop down list that populates based upon cell values

1. place a combobox on the spreadsheet

2. assume that cell C8 is for blue value & C9 for red value

in the VBE under Private Sub Worksheet_SelectionChange(ByVal Target As
Range) paste this code

Dim BlueNo, RedNo
ComboBox1.Clear

BlueNo = Range("C8").Value
RedNo = Range("C9").Value

For i = 1 To BlueNo
ComboBox1.AddItem ("Blue")
Next i

For i = 1 To RedNo
ComboBox1.AddItem ("Red")
Next i

Bryan Hessey · Aug 21, 2006

Another method, assuming that your colour is in column B from B1
downwards, and the number of required occurances is n column C from C1
downwards, using columns E F and G to create your list in column E:

in E1 put:
=INDIRECT("b"&G1)

in F1 put:
=SUM(C$1:C1)

in G1 put a number 1
in G2 put:
=IF(ROW()>INDIRECT("f"&G1),G1+1,G1)

and formula drag columns E F and G to cover the required total of
entries.

when the list is complete you can copy column E and Paste Special =
Values then remove any non-required columns.

HTH

Maven · Aug 21, 2006

This has been plaguing me. It will take some playing around to se
which solution is ideal for my project, but you've all been a hug
help.

Thanks

scottfoxall · Aug 21, 2006

Sorry Maven
I misread your question.

If you dont want a Dropdown list but want to populate cells with the colour
values without having to type in and copy a formula down the sheet. you
could use the code I sent before but with the following changes:

Again C8=BLUE & C9=RED
List starts at say D9
B8 ="BLUE"
B9="RED"

--------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim BlueNo, RedNo, count

Columns("D

").ClearContents

BlueNo = Range("C8").Value - 1
RedNo = Range("C9").Value - 1
count = 0

For i = 1 To BlueNo
Range("D9").Offset(count, 0).Value = Range("B8").Value
count = count + 1
Next i

For i = 1 To RedNo
Range("D9").Offset(count, 0).Value = Range("B9").Value
count = count + 1
Next i

End Sub

Maven · Aug 22, 2006

scottfoxall said:
If you dont want a Dropdown list but want to populate cells with th
colour
values without having to type in and copy a formula down the sheet
you
could use the code I sent before but with the following changes:

I'm trying out all the suggestions, but I'm having trouble with thi
one. I have the code in, but can you tell me how I should go abou
using the code? Should I create a macro with this code, or is ther
another way to go about this? Sorry - I'm still learning

scottfoxall · Aug 22, 2006

You can run this code whenever the is a change in a value made on the
spreadsheet.

EG:

Say your list is on sheet1

Press (Alt+F11)

You are now in the VBE (Visual Basic Editor)

You should see on the left VBAProject1 "#####"
Where #### is your file name

make sure this is expanded out so you can see:
Worksheet1("Sheet1")
Worksheet2("Sheet2")
Worksheet3("Sheet3")
ThisWorkbook

If you want to run the code on the event of change value in sheet1:

Double click Sheet1

You are now in the code window. This should already be on the event value
change i.e:
---------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
------------------------------------------------------------------------

If not, then there are two pull downs at the top of the code window. If you
see "General" change this to Worksheet. Now you should see the above? If
not select it from the list of events at the top right of the code window.

Paste the code in between the above statements. eg

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'********PUT THE CODE HERE*****

End Sub

Hope that helps?

scottfoxall · Aug 22, 2006

CODE EXPLAINED:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'*******THIS DEFINES THE VARIABLES*******
Dim BlueNo, RedNo, count

'*******THIS CLEARS THE LIST ASSUMING ITS ALL IN COLUMN D*******
Columns("D

").ClearContents

'*******THIS GETS THE VALUES FROM THE SPREADSHEET RANGE MEANS CELL******
BlueNo = Range("C8").Value
RedNo = Range("C9").Value

'*******THIS SETS A COUNTER TO 0******
count = 0

'******THIS WILL LOOP AROUND THE CODE BLUENO OF TIME (THE VALUE YOU HAVE FOR
BLUE)
'******IT PUTS THE VALUE IN RANGE D9 BUT OFFSET BY THE VALUE OF COUNT WHICH
'******INCREASES BY ONE ON EACH LOOP
'******FIRST LOOP PUTS THE VALUE OF B8 WHICH IS "BLUE" IN RANGE/CELL D9
'******SECOND LOOP PUTS THE "BLUE" INTO D9 OFFSET(COUNT,0) WHICH IS CELL D10
'******OFFSET(Y,X) ON EACH LOOP Y ("COUNT") INCREASES BY 1
For i = 1 To BlueNo
Range("D9").Offset(count, 0).Value = Range("B8").Value
count = count + 1
Next i

'******SAME AS ABOVE BUT FOR RED VALUES
For i = 1 To RedNo
Range("D9").Offset(count, 0).Value = Range("B9").Value
count = count + 1
Next i

End Sub

Can I create a list this way?

Maven

MyVeryOwnSelf

scottfoxall

Bryan Hessey

Maven

scottfoxall

Maven

scottfoxall

scottfoxall