loop macro

[cacique]

Hi, i'm new in this forum and Looking for some help...

I need a macro to make a list of the fiber color code, starting by th
color in the selected cell and keep repeating in a row as many times i
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 blu
again.
there is any way to do this.

thank yo

 

[Guest]

Hi,
Is the selected cell always in the same column? And can you give an
example of the sequence i.e can it start with any colour: is the following
valid?

Violet, Rose,Aqua, Blue, ...Yellow,Violet ...

 

[cacique]

hi and thanks for the help,

yes and yes.

the selected cell is allways the same and the sequence can start with
any color..

 

[Mike Fogleman]

Is this what you are saying? You will enter a number in Cell A2 that
represents the number of fibers to list per row. At the beginning of that
row you have already entered a starting color. So if you select A12 which
has Grey in it and there is a 4 in A2, you want the next 4 colors in B12 to
E12? White through Yellow. That would be 5 fibers in that row. Do you also
want to indicate the Tube color, Bundle color, etc.? Because when you use up
the first 12 fibers the Tube color changes to Orange, and when you have used
12 Tubes (144 fibers) then the Bundle changes to Orange. Or do you not care
about which Tube, Bundle the fiber is in, you just want the fiber colors to
loop from a starting point on each row.

Mike F
Cable Guy

 

[cacique]

hi, Mike


yes that's what I want.

the Tube or ribbon color change, will be using the same formula but
with different number source from different cell.

I think, I can figure out after i have the macro to loop the colors

 

[cacique]

Any One that can help me!!
thanks!!!

 

[Mike Fogleman]

OK, this macro uses Excels Auto_Fill from a list feature, so you will need
to create a Custom List of the fiber colors. You do that in
Tools/Options/Custom Lists. Once you have that, put this code in a general
module. Assign it to a worksheet button if you wish for ease of use.

Sub Paste_fibers()

Dim fromhere As String
Dim tohere As String
Dim fibcnt As Long

fibcnt = Range("A2").Value
fromhere = ActiveCell.Address
tohere = ActiveCell.Offset(0, fibcnt).Address
Selection.AutoFill Destination:=Range(fromhere & ":" & tohere)

End Sub

Mike F

 

[Mike Fogleman]

I would think that you would want to include the starting fiber in the fiber
count for the row. In other words, if you have a 6 in A2 you would want only
6 fibers in that row, which would include the starting fiber, so the macro
should only add 5 more for a total of 6. If so, modify one line like this:

fibcnt = Range("A2").Value - 1

That will make "tohere" one column shorter.

Mike F