Copying 2/3 unique values from range of data but ramdomly - Is this a possible function in Excel VBA?

pedie · Mar 24, 2011

I am wanting to copy 2/3 unique values from range of data from B col till last row but ramdomly - Is this a possible function in Excel VBA? The data will contain > 1000 rows of datas.

Thanks for helping in advance.

mikerickson · Mar 27, 2011

Substituting this for the same function above should speed things up a bit.

Code:

Function Array1to(N As Long) As Variant
    Static countingNumbers() As Long
    Dim workRRay As Variant, i As Long
    
    On Error GoTo StartCounting
    If UBound(countingNumbers) < N Then
        On Error GoTo 0
        workRRay = UBound(countingNumbers)
        ReDim Preserve countingNumbers(1 To N)
        For i = workRRay + 1 To N
            countingNumbers(i) = i
        Next i
    End If
    On Error GoTo 0
    
    workRRay = countingNumbers
    ReDim Preserve workRRay(1 To N)
    
    Array1to = workRRay
    Exit Function
StartCounting:
    ReDim countingNumbers(1 To 1)
    countingNumbers(1) = 1
    Err.Clear
    Resume
End Function

pedie · Mar 27, 2011

Code:

[/FONT]
[FONT=Courier New]choiceArray = selectFrom(Array1to(batchCount), selectionCount)[/FONT]
[FONT=Courier New]

It erros in the above line. "Sub or Function not defined..."
I copied and paste all code along with the function but still error.

I have sheets named accordingly.

Thanks again Mike.

mikerickson · Mar 27, 2011

Both functions, Array1To and selectFrom, are in the code in post #11.
Have you checked that 0 < selectionCount <= batchCount?

Is this in a normal module?

pedie · Mar 27, 2011

Yes, it is in normal module & i have both the function code and the code itself...

Is the data suppose to be organised in some way before running the code?

mikerickson · Mar 27, 2011

The data is supposed to be sorted ascending.
But if the data isn't, it wouldn't cause that error.

pedie · Mar 27, 2011

Hi, again. Sorry...still doesn't work. My dummy Table looks like this.

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #4a452a; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18 width=64>

Adam

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #4a452a; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>

Adam

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #4a452a; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>

User 9

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #4a452a; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>

User 9

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #4a452a; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18>

Adam