Array

[Terry VanDuzee]

Hello
How can I do this? :

Im trying to set the values of a group of cells to an array.
Set rng2 = Array(Range("B4" & .Value), Range("B5" & .Value))
The actual range is B4:I4, but I cannot even get it to work with 2 cells. I
tried set rng2=Array(Range("B4:I4").value, but Im sure ya'll know that did
not work either.

Thank you so much
Terry V

 

[JS]

hi

this will create an array for Range("B4:I4")

Sub Test()
Dim rng As Variant
rng = Range("B4:I4")
End Sub

 

[Otto Moehrbach]

Terry
Explain to us what you are trying to do. Why do you want to set up this
array? What do you want to do with it once you get it set up? HTH Otto

 

[Terry VanDuzee]

What I am doing is:
In B4:I4, I have numbers in the cells.
In B5:G5, I want to pull at random, 6 of the numbers from the range
B4:I4........ it would be nice if I could get them to sort smallest to
largest too... but thats not necessary.

All Im doing here is simply experimenting with manipulation of data.

B4=5, C4=10, D4=12, E4=15, F4=22, G4=25, H4=30, I4=100

B5 may = any of the numbers in the range B4:I4
C5 may = any of the numbers in the range B4:I4
etc etc
G4 may = any of the numbers in the range B4:I4

Im looking for a way for XL to randomly select a value from a cell within
the range and return it in the "Activecell". Which I will set by the
..offset(0,1)

Thank you so much.
Terry V

 

[Tom Ogilvy]

This isn't necessarily the most efficient way of doing it, but it should be
easy to understand:

Sub Remove2()
Dim icnt As Long, idex As Long
Dim varr As Variant
Dim rng As Range, i As Long
Randomize
icnt = 0
Set rng = Range("B4:I4")
varr = rng.Value
Do
idex = Int(Rnd() * 8 + 1)
If Not IsEmpty(varr(1, idex)) Then
varr(1, idex) = Empty
icnt = icnt + 1
End If
Loop Until icnt = 2
icnt = 0
For i = 1 To 8
If Not IsEmpty(varr(1, i)) Then
Range("B5").Offset(0, icnt).Value = varr(1, i)
icnt = icnt + 1
End If
Next

End Sub

Regards,
Tom Ogilvy