PDA

View Full Version : How to randomise looping of variables



xlUser
09-29-2011, 08:16 AM
Hi,

I have the code below that takes a column of data from a range and pastes it elsewhere . Currently it does this in order of column, ie C, D, E, F etc

What I would like to do is randomise the order of column extraction. So it may extract column D first and then column K and then column C instead of C, D, E, F etc.

Is this posibble to do?

Thanks

XL




z = Sheets("X Variables").Cells(1, 200).End(xlToLeft).Column
For i = 3 To z
Range(Cells(1, i), Cells(1000, i)).Copy

Sheets("Search").Select
Range("B1").PasteSpecial Paste:=xlPasteValues

Bob Phillips
09-29-2011, 08:28 AM
Dim vecCols As Variant
Dim col As Long
Dim idx As Long
Dim tmp As Long
Dim i As Long

ReDim vecCols(1 To 26)
Do

col = Int(Rnd() * 26 + 1)
tmp = 0
On Error Resume Next
tmp = Application.Match(col, vecCols, 0)
On Error GoTo 0
If tmp = 0 Then

idx = idx + 1
vecCols(idx) = col
End If
Loop Until idx >= 26

For i = 1 To 26

If vecCols(i) > 2 Then

Cells(1, vecCols(i)).Resize(1000).Copy
Worksheets("Search").Range("B1").PasteSpecial Paste:=xlPasteValues
End If
Next i