PDA

View Full Version : How to fill the number in the cells.



clif
06-30-2012, 06:40 AM
Any code can make the following pattern? Thanks!


* A B C D E
1 1 2 3 4 5
2 2 3 4 5 1
3 3 4 5 1 2
4 4 5 1 2 3
5 5 1 2 3 4

fredlo2010
06-30-2012, 08:24 AM
My first try

Cells(1, 1).Resize(, 6).Value = Array(Chr(42), "A", "B", "C", "D", "E")
Cells(2, 1).Resize(, 6).Value = Array(1, 1, 2, 3, 4, 5)
Cells(3, 1).Resize(, 6).Value = Array(2, 2, 3, 4, 5, 1)
Cells(4, 1).Resize(, 6).Value = Array(3, 3, 4, 5, 1, 2)
Cells(5, 1).Resize(, 6).Value = Array(4, 4, 5, 1, 2, 3)
Cells(6, 1).Resize(, 6).Value = Array(5, 5, 1, 2, 3, 4)

mikerickson
06-30-2012, 10:33 AM
You could put =MOD(ROW()+COLUMN()-2, 5)+1 in A1 and drag down and right

fredlo2010
06-30-2012, 11:06 AM
You could put =MOD(ROW()+COLUMN()-2, 5)+1 in A1 and drag down and right

wow man how did you find the logical pattern here?

If you don't wanna drag and using mikerickson brilliant idea then

Range("A1:E5").FormulaR1C1 = "=MOD(ROW()+COLUMN()-2, 5)+1"