CODEWITHSUNDEEP
excelvba
Rich Prag
Rich Prag

Reputation: 133

VBA to list number combinations sequentially in two columns

I would like to list the possible combinations of numbers 1 to 26 in columns A and B, in the following format:

    1 1
    1 2 
    1 3
    1 4
    ...
    1 25
    1 26
    2 1
    2 2
    2 3
    ...
etc

For Column A, I could have:

Range("A1:A26") = 1
Range("A27:A52") = 2
etc

But this seems long winded and there must be a better way of doing this.

I have found this code as an answer to another question which gives

Range("A1")=-500
Range("A1").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=1, Stop:=500, Trend:=False

as a way to list numbers sequentially but I would need to amend it to reach 26 and then start again from 1, all the way to the end of the list in Column A. How can I achieve this?

Upvotes: 0

Views: 117

Answers (2)

AJD
AJD

Reputation: 2438

A VBA solution as an alternative to the Excel formulas given in the previous reply. (Untested)

MyRow = 1 ' or wherever you want to start
MyCol = 1 ' or wherever you want to start
For A = 1 to 26
  For B = 1 to 26
    Cells(MyRow,MyCol).Value = A
    Cells(MyRow,MyCol+1).Value = B
    MyRow = MyRow + 1
  Next B
Next A

The advantage of this approach instead of the formula described above is that it is not dependent on where you place the data. It can also be adapted if you want (e.g.) combinations of 5 to 24, or even A is 1 to 26 and B is 1 to 10.

But, the formula described in the previous answer is also a cool way of doing it.

If you changed:

For A = 1 to 26
  For B = 1 to 26

to 

For A = 1 to 26
  For B = A to 26

then this would be useful for a non-directional combination (e.g. if [1,2] is the same as [2,1]).

Upvotes: 0

Alex P
Alex P

Reputation: 12487

Try these formulas and then drag down as far as you need (row 676):

A1 =ROUNDUP(ROW()/26,0)

B1 =IF(MOD(ROW(),26)=0,26,MOD(ROW(),26))

Upvotes: 1

Related Questions