如何显示在VBA / Excel中的一个洗牌数组条目 [英] How to show entries of a shuffled array in VBA / Excel

问题描述

我一直在试图重新洗牌11整数阵列和阵列洗牌到Excel粘贴。我已经发现了一些code，几乎我想要做什么，但不是返回数组的洗牌项则显示洗牌行号（西A）和用于排序（西B）的随机数。

I've been trying to shuffle an 11-integer array and paste the shuffled array into excel. I've found some code that almost does what I want, but instead of returning the shuffled entries of the array it shows the shuffled row numbers (Col A) and the random numbers used for sorting (Col B).

我是新来的VBA和想不出来返回对应于柱A的洗牌行号数组的进入，如果是有道理的？我只希望看到的洗牌条目，而不是行号或随机数。有意义的希望！我使用的是：

I'm new to VBA and can't figure out to return the entry of the array that corresponds to the shuffled row number in Col A, if that makes sense? I only want to see the shuffled entries and not the row numbers or random numbers. Hope that makes sense! I'm using:

Sub Shuffle()

Dim intNumbers(1 To 11) As Integer

'the list of numbers I want to shuffle 
intNumbers(1) = 1
intNumbers(2) = 1
intNumbers(3) = 1
intNumbers(4) = 1
intNumbers(5) = 1
intNumbers(6) = 1
intNumbers(7) = 2
intNumbers(8) = 5
intNumbers(9) = 6
intNumbers(10) = 3
intNumbers(11) = 7

Dim rngNumbers As Range
Dim rngRandom As Range
Dim rngSort As Range
Dim rngTemp As Range



Set rngNumbers = ActiveSheet.Range("A1:A11")
Set rngRandom = ActiveSheet.Range("B1:B11")
Set rngSort = ActiveSheet.Range("A1:B11")



Randomize
 ' store number and random sequence
For Each rngTemp In rngRandom
    rngTemp = Rnd()
    rngTemp.Offset(0, -1) = rngTemp.Row
Next

rngSort.Sort key1:=rngSort.Columns(2)
For Each rngTemp In rngNumbers
    intNumbers(rngTemp.Value) = rngTemp

Next



End Sub

我可以看到这是什么code正在做，但不能弄清楚如何得到它做想什么我。仍然有很多东西需要学习！

I can see what this code is doing but can't figure out how to get it to do what I'd like. Still got a lot to learn!

推荐答案

试试这个code。它会留在A列中的原始行，排序的随机数A>以Z列B和C列：您的数组的索引，依赖于行号

Try this code. It will leave the original rows in column A, sorted random numbers A>Z in column B, and in column C: the index of your array, dependent on the row number.

Sub Shuffle()

Dim intNumbers(1 To 11) As Integer

'the list of numbers I want to shuffle
intNumbers(1) = 1
intNumbers(2) = 1
intNumbers(3) = 1
intNumbers(4) = 1
intNumbers(5) = 1
intNumbers(6) = 1
intNumbers(7) = 2
intNumbers(8) = 5
intNumbers(9) = 6
intNumbers(10) = 3
intNumbers(11) = 7

Dim rngNumbers As Range
Dim rngRandom As Range
Dim rngSort As Range
Dim rngTemp As Range



Set rngNumbers = ActiveSheet.Range("A1:A11")
Set rngRandom = ActiveSheet.Range("B1:B11")
Set rngSort = ActiveSheet.Range("A1:B11")



Randomize
 ' store number and random sequence
For Each rngTemp In rngRandom
    rngTemp = Rnd()
    rngTemp.Offset(0, -1) = rngTemp.Row
Next

rngSort.Sort key1:=rngSort.Columns(2)
For Each rngTemp In rngNumbers
    rngTemp.Offset(0, 2).Value = intNumbers(rngTemp)

Next



End Sub

这篇关于如何显示在VBA / Excel中的一个洗牌数组条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！