looping colors

[cacique]

I need a macro to make a list of the fiber color code, starting by the
color in the selected cell and keep repeating in a row as many times is
specified in another cell (A2).

The color code is

Blue
Orange
Green
Brown
Grey
White
Red
Black
Yellow
Violet
Rose
Aqua

This is the fiber color code, and after aqua start over whit blue
again.
there is any way to do this.

thank you

 

[Tom Ogilvy]

Sub Abcdef()
Dim v As Variant, sColor As String, num As Long
Dim lb As Long, idex As Variant
Dim ub As Long
v = Array("Blue", "Orange", "Green", "Brown", _
"Grey", "White", "Red", "Black", "Yellow", _
"Violet", "Rose", "Aqua")

sColor = StrConv(ActiveCell.Value, vbProperCase)
num = Range("A2")
lb = LBound(v)
ub = UBound(v)
idex = Application.Match(sColor, v, 0)
If IsError(idex) Then Exit Sub
If lb = 1 Then idex = idex + 1
If idex > ub Then idex = lb
For i = 1 To num
ActiveCell.Offset(0, i) = v(idex)
idex = idex + 1
If idex > ub Then idex = lb
Next
End Sub

 

[Gord Dibben]

Assuming your list of colors is in C1:C12

Sub copystuff()
Dim i As Long
Application.ScreenUpdating = False
howmany = Range("A2").Value
For i = 1 To howmany
Range("C1:C12").Copy Destination:= _
ActiveSheet.Cells(Rows.Count, 3).End(xlUp) _
.Offset(1, 0)
Next i
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP

 

[Dana DeLouis]

If you do this often, you may want to add a custom list. Then you can just
enter a color, and drag down.

Sub Demo()
Dim v
Dim HowMany As Long

HowMany = 50

v = Array("Blue", "Orange", "Green", "Brown", _
"Grey", "White", "Red", "Black", "Yellow", _
"Violet", "Rose", "Aqua")

Application.AddCustomList ListArray:=v

'// Start with a middle color
[A1] = "Red"
[A1].AutoFill [A1].Resize(HowMany)

'// Keep List, or delete it here
With Application
.DeleteCustomList (.GetCustomListNum(v))
End With
End Sub

HTH :>)