Assigning values from a selected range to individual variables

D

Dr. Schwartz

A user selects a range (eg: Range("A1:C1")) and runs a macro. The macro should then assign the value from each cell to its own variable. So cell A1 = 2, B1 = 4 and C1 = 6 and the variables should end up with NumA = 2, NumB = 4 and NumC = 6.

The problem is that I want to be able to handle any size of selected range the user would make and then assign the given number of values to an individual variabel.

Can anybody help out?

Thank you
Schwartz
 
T

tolgag

user selects a range (eg: Range("A1:C1")) and runs a macro. The macr
should then assign the value from each cell to its own variable. S
cell A1 = 2, B1 = 4 and C1 = 6 and the variables should end up wit
NumA = 2, NumB = 4 and NumC = 6.

The problem is that I want to be able to handle any size of selecte
range the user would make and then assign the given number of values t
an individual variabel.

You should try to use an array, which you expand in a dinamic way :

Dim intValues() as Integer
Dim r as Range
Dim intStartRow as Integer, intEndRow as Integer, intColumn as Integer
Dim i as Integer, j as Integer

set r = Selection
if r.Columns.Count>1 then
msgbox ("The Selection must be made in one column !!")
exit sub
end if

ReDim intValues(r.Rows.Count)

intColumn = r.Colums(1).Column
intStartRow = r.Rows(1).Row
intEndRow = r.Rows(r.Rows.Count).Row
j=1
For i = intStartRow To intEndRow

intValues(j) = cells(i,intColumn)
j=j+1
Nex
 
T

tolgag

sorry, I forgot...

to have a more flexible solution :

If you want to be able to handle multicolumn selection :
1. remove the if block checking the number of columns
2. make a multidimensional ReDim. The second dimension should set th
number of columns in the selection.
3. change the loop, so you can peek the selected range through column
 
B

Bob Phillips

Schwartz,

What is the criteria for assigning values. In your example, you use the even
numbers.

You could try something like

i=1
For each cell In Selection
cell,Value = i*2
i = i + 1
Next cell
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Dr. Schwartz said:
A user selects a range (eg: Range("A1:C1")) and runs a macro. The macro
Click to expand...
should then assign the value from each cell to its own variable. So cell A1
= 2, B1 = 4 and C1 = 6 and the variables should end up with NumA = 2, NumB =
4 and NumC = 6.
The problem is that I want to be able to handle any size of selected range
Click to expand...
the user would make and then assign the given number of values to an
individual variabel.
 
P

Patrick Molloy

You don't need to make it so complex. You can load a
variant directly from a range

eg
1)
DIM MyArray as Variant
MyArray = Range("G1:Y34")

2)
DIM MyArray as Variant
MyArray = Selection

You can easily find the bounds using the UBOUND ad LBOUND
functions.

Patrick Molloy
Microsoft Excel MVP
 
T

tolgag

If you use this method, don't forget to give correct dimensions while
you're reading your array, since the variant array is multidimensional.
Like this :

myarray(rownumber,1)

if your range would have 2 columns, then it supposed to look like this,
if you want to read a value from the second column :

myarray(rownumber,2)
 
Top