15,397,084 members
See more:
The image shows the rows and columns
I have code to loop the value like in first take value from column no 1,1,1,1,1,1,1,1,1,1,1,1 then take value from 1,1,1,1,1,1,1,1,1,1,1,2 then 1,1,1,1,1,1,1,1,1,1,1,3 then 1,1,1,1,1,1,1,1,1,1,2,1 then 1,1,1,1,1,1,1,1,1,1,2,2 then 1,1,1,1,1,1,1,1,1,1,2,3 then 1,1,1,1,1,1,1,1,1,1,3,1 and so on....

What I have tried:

The code is
VB
```Option Explicit

Sub CountBase3()

Dim R As Range

Set R = Range("F2:H13")

Dim Rows As Long, Columns As Long, Maxcount As Long

Rows = R.Rows.Count()

Columns = R.Columns.Count()

Maxcount = Columns ^ Rows

Debug.Print ("Maxcount=" & Maxcount)

'Copy range to array

Dim data As Variant

data = R

Dim counter As Long, row As Long, column As Long, counterShifted As Long

Dim Values()

ReDim Values(Rows)

'Loop over selections - 531441 to find all the sets but just look at the first few for now

For counter = 0 To 8

counterShifted = counter

For row = Rows To 1 Step -1

'Get column from right hand digit of counter

column = counterShifted Mod Columns + 1

Values(row) = data(row, column)

'Shift counter by dividing by number of columns (3)

counterShifted = counterShifted \ Columns

Next row

'Display current selection

Debug.Print ("Set " & counter + 1)

For row = 1 To Rows

Debug.Print (Values(row));

Next row

Debug.Print ("")

Next counter

End Sub```

and
output is
VB
```Maxcount=531441
Set 1
700  500  500  500  450  400  400  450  300  250  200  100
Set 2
700  500  500  500  450  400  400  450  300  250  200  150
Set 3
700  500  500  500  450  400  400  450  300  250  200  200
Set 4
700  500  500  500  450  400  400  450  300  250  250  100
Set 5
700  500  500  500  450  400  400  450  300  250  250  150
Set 6
700  500  500  500  450  400  400  450  300  250  250  200
Set 7
700  500  500  500  450  400  400  450  300  250  300  100
Set 8
700  500  500  500  450  400  400  450  300  250  300  150
Set 9
700  500  500  500  450  400  400  450  300  250  300  200 ```

so number of possibility is 3^12 = 531441 witch is large and time consuming so if have to find like possibility no 50000 or any possibility number within 531441 so if any idea how to do it
Posted
Updated 14-May-21 21:07pm
v2
Patrice T 14-May-21 11:06am

You forgot to describe what you try to do.
SeanChupas 14-May-21 12:23pm

I do not understand.
Member 14705972 15-May-21 3:48am

so I update the question as written in Bold at end as loop runs it start from 0 and the output is Set1,set2,set3,... but I wont to find any number like set50000 how to find value of set50000 or any number
Patrice T 15-May-21 4:28am

You forgot to explain what you want to do faster.
We don't know what you talk about.
Patrice T 15-May-21 4:38am

"Find the value without looping"
find what value ?
Member 14705972 15-May-21 5:08am

in the output first value is
Set 1
700 500 500 500 450 400 400 450 300 250 200 100
second value is
Set 2
700 500 500 500 450 400 400 450 300 250 200 150
and so on...
CHill60 15-May-21 10:52am

From the use of Range and Rows in your code it looks like this is VBA not VB15, so your options will be limited. You should tag your question appropriately if this is the case.

Based on your comments and sample data all you are trying to do is transpose the columns of data into rows. But I doubt that is really what you are trying to achieve, and I really can't be bothered to try to unravel what your code is doing.

You are going to have to find a way to sensibly explain what it is you are trying to do