Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array
hi
this will create an array for Range("B4:I4") Sub Test() Dim rng As Variant rng = Range("B4:I4") End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex conditional summing - array COUNT works, array SUM gives#VALUE | Excel Worksheet Functions | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) |