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]

And... Did it not work?

I just have your code and run it and I see how it run of the output with the do while, and how For Each doesn't do it because it doesn't output all the values.
But I have to dig deeper into the collection and object to know more about it and then to get a better understanding of it and how you used it in your
codes.

My last out put of only 5 because of this code here;

Loop While n <= cntHIT

But my last code, that way works too but not as fancy as your code or as elegance as your code.
I need to review object and collections more and review your codes more and then I should be able to advance
your code to do what I need. But your code looks good. Thanks for sharing !!

 

[JohnSeito]

Loop While n <= cntHIT

Ok, I just noticed this code is not good, because I am reading 11 numbers in there, and then running this code, I am just removing 5 of the items.
So there are still 6 items in the collection, and then when I run a new sets of 11 numbers, I can't input it to the collection because the space is taken.

Is there a way I could clear the collection with one code or do I need to run the loop to clear all of it? Thanks

 

[Cool blue]

Is there a way I could clear the collection with one code or do I need to run the loop to clear all of it? Thanks

That's a good question. In your case you don't have any Objects in the collection, only scalar variables, so you don't have to loop through the members to clean up. You can just set the collection to nothing.

If you are outputting only 5 items then you can go back to the For loop structure and use the output cells to make the indexing easy.

Sub loadC()
[COLOR=#008000]'Assumes that the input range is selected in the active sheet
'Outputs random sellection to outRange[/COLOR]

Dim outRange As Range, r As Range, C As Collection
Dim mObj As Object
Dim mClass1 As Class1

[COLOR=#008000]'Load Collection based on selection for example
'Include a Key (r.Text) for easy access[/COLOR]
Set C = New Collection
For Each r In Selection
    C.Add r.Value, r.Text
Next r

Set mClass1 = New Class1
mClass1.i = C.Item(1)

[COLOR=#008000]'Output random choice from collection using the Key (r.Test)[/COLOR]
Set outRange = Range(Cells(1, 1), Cells(5, 1)) '...for example
For Each r In outRange
    r.Value = C.Item(Int(Rnd * C.Count + 1))
    C.Remove r.Text
Next r
[COLOR=#008000]'Clean up for next time[/COLOR]
Set C = Nothing
End Sub

Again, I'm using the natural structure of the language to manage all of the indexing. I don't even have to think about how to reconcile different indices, its all automatic.

 

[xenou]

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

Why are we hard coding all these "variables" (rowFour, colFour, rowTest). We don't even use I in this for loop so it just does the same thing over and over again? And why do we stop at lastCol -1 instead of lastCol?

I really would like to erase all the code and start from a simple *in English* explanation of what we want to do. It sounds very simple but this thread is exceedingly confusing.

I am looking at the code from post #18. This For loop looks wrong and the use of the variables doesn't make sense:

Sub testCode()

lastCol = Cells(215, Columns.Count).End(xlToLeft).Column
lastrow = Cells(Rows.Count, "A").End(xlUp).Row

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

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

n = 1
Row = 216
Do
    I = Int(Rnd * C.Count + 1)
    Cells(Row, colSHOW + 1) = C.Item(I)
    C.Remove (I)
    Row = Row + 1
    n = n + 1
Loop While n <= cntHIT
colSHOW = colSHOW + 1

End Sub

 

[JohnSeito]

If you are outputting only 5 items then you can go back to the For loop structure and use the output cells to make the indexing easy.

Ok, still trying to figure out why if I output 5 I could For Loop, is because For Loop stops at the last index in the collection and after that it doesn't
the For Loop ends right (this is just my assumption) ? So if this is the case and I am outputting 5 from the 11 in the collection, what if the 3 output is
the last index in the collection, wouldn't the For Loop end then and I will never get to 4 or 5.

Again, I'm using the natural structure of the language to manage all of the indexing. I don't even have to think about how to reconcile different indices, its all automatic.

Ok, I am still getting to this point, so what is the natural structure of the language ?

Thanks again for your info, that was good to know.

 

[JohnSeito]

Why are we hard coding all these "variables" (rowFour, colFour, rowTest). We don't even use I in this for loop so it just does the same thing over and over again? And why do we stop at lastCol -1 instead of lastCol?

those variable, rowFour, colFour, rowTest, etc is where the datas are in the excel sheet, and where I pick up the values to input into the collection. And I use lastCol-1 instead of lastCol because the very last value is
irrelevant, it is irrelevant because I created a pivottable and the pivotable's last column has Grand Total and I don't want the column of the grand total.

I really would like to erase all the code and start from a simple *in English* explanation of what we want to do.

What I am trying to do is just a section of what my overall goal of what I am trying to do. From the beginning I have this big list,
this big list is like 20k lines and can vary depending on the load and input to it.

So I have to count the number of instance of items in this big list, so I can't use looping to counting them because it would take a long time to
do that, and it would be complicated to so I just use pivot table to count them, and then after that I just find out the columns of where the counts
are, because some items doesn't have any counts so the space is blank. I only care about the columns that I want to count so that is where this step comes in
I input the columns that has count in a collection, but doesn't pick out the last value bc the pivot is GRAND total.

I don't know if you guys creates pivot table so it counts it for you without having a macro and looping to create a count. That would
be too much loop and it will take a long time. So I just set up a pivot table to do the count and use that pivot table
as reference to pick up data from the big list (hope you know what I mean).

The count of something (1% - I already did this calculation) of it is how many I want to pick out from the big list, that is what I am trying to do.

Sorry xenou if it sounds confusing ! but thanks for the info, and thanks for this website that makes it easier for me to find some information that I don't know of !!
I still have lots to go because I know all of Excel and VBA.

 

[JohnSeito]

is where the datas are in the excel sheet

Actually is where the pivot table is located in the excel spreadsheet after creating the pivot table from the big list, so
I could see the count without using a macro to count and create the count list.

Thanks again for this website, it's helpful to many people like myself.

 

[xenou]

The count of something (1% - I already did this calculation) of it is how many I want to pick out from the big list, that is what I am trying to do.

Are you trying to take random samples?

Also, why would you expect the "Counts" to be unique? can't two items have the same count?

 

[JohnSeito]

Are you trying to take random samples?

Yes, why? it doesn't have to be 1%, it could be any percentage, 20, 30, 45 etc, discretion of the user. just saying ! i am making a form for input
but that is later, after I am at 95 or 98% level of completion

Also, why would you expect the "Counts" to be unique? can't two items have the same count?

The count is not unique, sorry was wondering where you get counts as unique, the columns is unique (i am only putting the columns in a collection).
two items will be count together (from pivot table - pivot table counts like terms together), but it can be the same count with a different items.

Thanks !!

 

[xenou]

So when you say you want output to be unique, you mean unique items, not unique counts? But you are only outputting one number. If that is a count, how do you know what it is a count of? And why does it have to be unique (if it selected two items with the same count, then then you will have two duplicate counts, but they are counts of different things so not really duplicates). What is the output supposed to be?