Read a Range into an Array

[Paul Black]

Hi everyone,

In a Worksheet named "Input" and in Cells "B3:G?" I have 6 number
groups. How can I ...

( 1 ) Put each of the 6 number groups into an array when I don't know
what the last row number is.
( 2 ) Find the maximum value in any of those cells and attach it to a
variable named MaxVal.

.... so once I have done this I can then loop through each 6 number
group and perform some calculations.

Thanks in Advance.
All the Best.
Paul

 

[Guest]

Dim rng as Range, maxVal as Long, v as Variant
Dim s as String, i as Long, j as Long
set rng = Range("B3:G" & Range("B3").End(xldown).row)
maxVal = Application.Max(rng)

v = rng.Value

for i = 1 to Ubound(v,1)
s = ""
for j = 1 to 6
s = s & v(i,j) & ","
next j
msgbox "Row " & i + 2 & ": " & left(s, len(s)-1)
Next i

 

[Peter T]

If the suggestion I gave in reply to your previous and virtually identical
question didn't help, explain why not and more detail of what you want to
do.

See thread -
Find Last Cell in Range, 8-Aug-07

Regards,
Peter T

 

[Paul Black]

Thanks for the quick response Tom,

How would I be able to use your code ( forget the maxVal ) to loop
through the 6 number groups in the Worksheet so the following Sub will
work please. At the moment ...

SetGroup 1, 1, 2, 3, 4, 5, 9
SetGroup 2, 1, 3, 5, 6, 7, 9
SetGroup 3, 2, 4, 5, 6, 7, 8

.... etc is hard coded.

Private Sub SetGroup(ByVal Index As Long, ParamArray Num())
Dim vlu, cell As Long, bit As Long
Dim dgt As Digits, Wh As Wheel

For Each vlu In Num
cell = vlu \ 8
bit = vlu And 7
dgt.B(cell) = dgt.B(cell) Or (2 ^ bit)
Next
LSet Wh = dgt
WHL(Index).A = Wh.A
End Sub

Thanks in Advance.
All the Best.
Paul

 

[Paul Black]

Hi Peter,

My appologies. Unfortunately I am unsure how to, or indeed if it can
be done, to incorporate your suggestion into the actual task I am
trying to achieve.

Thanks in Advance.
All the Best.
Paul