Array
 

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

Array
 

hi

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

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

Array
 

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" wrote in message
...
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

Array
 

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" wrote in message
...
Dim rng as Range
set rng = Range("B4").Resize(1,8)

for each cell in rng
msgbox cell.Address & " " & cell.Value
Next

Not sure where an array enters into the equation.

--
Regards,
Tom Ogilvy



Terry VanDuzee wrote in message
...
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

Array
 

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


Terry VanDuzee wrote in message
...
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" wrote in message
...
Dim rng as Range
set rng = Range("B4").Resize(1,8)

for each cell in rng
msgbox cell.Address & " " & cell.Value
Next

Not sure where an array enters into the equation.

--
Regards,
Tom Ogilvy



Terry VanDuzee wrote in message
...
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