Two identical code giving completely different result

[JohnSeito]

Hi guys,

Sorry to bother you, I have been looking at this line of code for a few hours now and
I can't seem to get why one code is giving a correct solution and the other one
not even close. They are both identical but there are some difference.

Just wondering if anyone could see why that is?

The program is suppose to pick up about 11 cell values, it then store these
cell values into a collection, and randomly picking out the 11 values without repeating
until there is no more to pick out.

Here is the code: (the top one doesn't give the correct result, it picks it out of the same ones and
the number doesn't seem to store correctly, the bottom codes works as it suppose to work.)

lastCol = Cells(215, Columns.Count).End(xlToLeft).Column

Dim C As New Collection, I As Integer, rdom As Integer

rowFOUR = 216
colFOUR = 2
'cntHIT = 0
rowtest = 216
    
For I = 2 To lastCol - 1
    If Cells(rowFOUR, colFOUR) <> "" Then
        Cells(rowtest, "W") = colFOUR
            num = Cells(rowtest, "W")
        C.Add CStr(num), CStr(num)
        rowtest = rowtest + 1
    End If
    
    colFOUR = colFOUR + 1
Next I

n = 0
Row = 216
Do

    I = Int(Rnd * C.Count + 1)
    Cells(Row, "X") = I
    C.Remove (I)
    
    Row = Row + 1
    n = n + 1
Loop While n <= 15 'numTimes >= 0

Dim C As New Collection, I As Integer

Row = 215
For I = 1 To 11
    num = Cells(Row, "W")
  C.Add CStr(num), CStr(num)
  Row = Row + 1
Next I

I = Int(Rnd * C.Count + 1)
x = 216

Do
    Cells(x, "x") = C.Item(I)
    C.Remove (I)
    I = Int(Rnd * C.Count + 1)
    x = x + 1
Loop Until x >= 250

 

[JohnSeito]

So when you say you want output to be unique, you mean unique items, not unique counts?

unique columns, I put the columns #'s in the collection, that is this step I am now posting on this board.
This columns is dynamic because pivot table is dynamic depending if you add more or less datas to the big list, so the columns
and rows of the pivot table can grow or shrink, the column in the pivot table is unique. I put the column numbers in a collection,
and then randomly choose them for the difference of this code: (code on the bottom - I just created this code)

so out of 11 columns from the pivot table, I only want two random column number, or three or four,
depending on the cell of secondvalue and firstvalue.

I only want this because I then want to deduct the counts from the pivot table because the % percentage is off.
the counts in the pivot table is calculated as a %. so for example in row 4 in the pivottable, and column 7 in the pivot
table, the count for this item is 995, 10% as an example of this is 99.5, but I calculated the whole number, so this is now 100.
so another item in another location in the pivot table can be 45, 10% is 4.5, but I calculated as 5. And if you do this to
all the items in the pivot table, you are going to be off with the % of the overall items from the whole list because
you are adding up and rounding up for all the items in the pivot table,

so the whole list has a total of 98514 for example, and 10% is 9851.4 and round it out is 9851. So this is true 10%, but the
% from the pivot calculation of each item can be over for example (just making up a number) 9945 as 10%. So that means
I have to deduct from my pivot table list to equal to the real % of the whole list. Sorry long story but hope you know what
I mean.

SecondValue = Cells(rowFOUR, "BU")
firstValue = Cells(rowFOUR, "BT")
LIMIT = SecondValue - firstValue

n is start of as 1

If n <= LIMIT Then

But you are only outputting one number.

not sure where you say 1 number from.

If that is a count, how do you know what it is a count of?

know count of from the pivot table by the first row, and first column.

What is the output supposed to be?

The first phase of the output should be give me the % of all the items in the pivot table list in number of what the user requested.
and all the number in the list base on % should equal to the over all percentage of the all the items in the pivot table (grand total from the last
row and column in the pivot table). something like this!!!

I am not randomly picking items out yet, I am just setting it up, then I will do that later after setting it up.

 

[JohnSeito]

so the whole list has a total of 98514 for example, and 10% is 9851.4 and round it out is 9851. So this is true 10%, but the
% from the pivot calculation of each item can be over for example (just making up a number) 9945 as 10%. So that means
I have to deduct from my pivot table list to equal to the real % of the whole list

I am doing random here is because I want to randomly deduct the list from the pivot table. It has 11 columns for example,
I want to deduct 4 columns from that list, and I don't know which one to deduct from, so I randomly deduct from it. This is where I am doing my randomization
for this post on this board now. Thanks !!

 

[xenou]

It sounds like you have a lot to work out on this, and you may be in over your head. It might make more sense to do this without vba if you can. That said, here's my attempt. I've just simplified it to a sample worksheet with the following set up:

Values in Cells A2:K2
Grand Total in Cell L2

That's it for the data.

For results I have hyphens in Cells B3:K3 (to act as a boundary, with test output below, in columns). Each test output is for a different sample size (from 1 to 10). Since there are only 7 cells with values, any sample size of 7 or higher just output all the cells with values (or rather, the column numbers of those cells).

Sample data (there is also a sample workbook link at the bottom of the page):

----------------------------------------------------------------------
|    | A        | B | C | D  |  E |  F |  G |  H | I  | J  |  K |  L |
----------------------------------------------------------------------
|  1 | Data:    | 1 | 2 |    |  4 |  5 |  6 |  7 |    |    | 10 | 35 |
|  2 |          |   |   |    |    |    |    |    |    |    |    |    |
|  3 | Results: | - | - | -  |  - |  - |  - |  - | -  | -  |  - |  - |
|  4 |          | 8 | 2 | 5  |  2 |  5 |  2 |  2 | 2  | 2  |  2 |    |
|  5 |          |   | 5 | 8  |  7 |  6 |  3 |  3 | 3  | 3  |  3 |    |
|  6 |          |   |   | 11 |  8 |  7 |  6 |  5 | 5  | 5  |  5 |    |
|  7 |          |   |   |    | 11 |  8 |  7 |  6 | 6  | 6  |  6 |    |
|  8 |          |   |   |    |    | 11 |  8 |  7 | 7  | 7  |  7 |    |
|  9 |          |   |   |    |    |    | 11 |  8 | 8  | 8  |  8 |    |
| 10 |          |   |   |    |    |    |    | 11 | 11 | 11 | 11 |    |
----------------------------------------------------------------------


Code:

[COLOR="Navy"]Sub[/COLOR] TEST_IT()
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] VBA.Collection
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] j [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] r [COLOR="Navy"]As[/COLOR] Range

    [COLOR="SeaGreen"]'//Test ColumnsWithValues function for different sample sizes[/COLOR]
    Sheet1.Range("B4:K10").ClearContents
    [COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] 10
        [COLOR="Navy"]Set[/COLOR] col = ColumnsWithValues(i)
        [COLOR="Navy"]Set[/COLOR] r = Sheet1.Cells(Rows.Count, i + 1).End(xlUp).Offset(1)
        [COLOR="Navy"]For[/COLOR] j = 1 [COLOR="Navy"]To[/COLOR] col.Count
            r.Value = col(j)
            [COLOR="Navy"]Set[/COLOR] r = r.Offset(1)
        [COLOR="Navy"]Next[/COLOR] j
    [COLOR="Navy"]Next[/COLOR] i
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Function[/COLOR] ColumnsWithValues(ByVal SampleSize [COLOR="Navy"]As[/COLOR] Long) [COLOR="Navy"]As[/COLOR] Collection
[COLOR="Navy"]Dim[/COLOR] LastColumn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ws [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] VBA.Collection
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] tmp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

    [COLOR="Navy"]Set[/COLOR] ws = ActiveSheet
    [COLOR="Navy"]Set[/COLOR] col = [COLOR="Navy"]New[/COLOR] VBA.Collection
    Randomize
    
    [COLOR="Navy"]With[/COLOR] ws
        
        [COLOR="SeaGreen"]'//Cells to check[/COLOR]
        LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
        
        [COLOR="SeaGreen"]'//Save column number of any cell with a value in it[/COLOR]
        [COLOR="Navy"]For[/COLOR] i = 2 [COLOR="Navy"]To[/COLOR] LastColumn - 1
            [COLOR="Navy"]If[/COLOR] .Cells(1, i).Value > 0 [COLOR="Navy"]Then[/COLOR]
                col.Add .Cells(1, i).Column
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]Next[/COLOR] i
    
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    
    [COLOR="SeaGreen"]'//Randomly remove items from collection until sample size is reached[/COLOR]
    [COLOR="Navy"]If[/COLOR] col.Count > SampleSize [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] col.Count > SampleSize
            tmp = Int((col.Count - 1 + 1) * Rnd + 1)
            [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] col.Count
            [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] tmp
            col.Remove (tmp)
        [COLOR="Navy"]Loop[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    
    [COLOR="SeaGreen"]'//return a collection with the column numbers of sample items[/COLOR]
    [COLOR="Navy"]Set[/COLOR] ColumnsWithValues = col
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

<a href="http://northernocean.net/etc/mrexcel/20140120_Book1.zip">SAMPLE WORKBOOK</a>
sha256 checksum: 168c41f5a40ce7b9ac62ad577963e89dd1fa57f7019d0ab6ac37ebeacfc5274e

 

[JohnSeito]

Thank you Xenou for your sample code and suggestion. I will look at it.

I still got to get good at collection and object but I will do that when I have some time !