Click here to Skip to main content
14,981,116 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Comments
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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900