Array Problem
 

Can you please tell me if it is possible to return a value of say the second
cell (B1) from the following statement...

Dim data As Variant

data = Range("A1:E1").Value

I have tried numerous ways but can not get the value of each cell in the
range which I need to examine before actioning

Thanks

TAM

Array Problem
 

data = Range("A1:E1").Cells(1,2).Value

or just

data = Range("B1").Value


--

HTH

RP
(remove nothere from the email address if mailing direct)


"TAM" wrote in message
. uk...
Can you please tell me if it is possible to return a value of say the
second
cell (B1) from the following statement...

Dim data As Variant

data = Range("A1:E1").Value

I have tried numerous ways but can not get the value of each cell in the
range which I need to examine before actioning

Thanks

TAM

Array Problem
 

If you have to examine each cell

for example, lets test each cell to see if it can be treated as if it has a
number (blanks would be treated a zero and that would be OK for the
example).

dim bNotOk as Boolean
bNotOK = False
for each cell in Range("A1:E1")
if not isnumeric(cell) then
bNotOk = True
exit for
end if
Next
if bNotOk then
msgbox "Bad Data, quitting"
exit sub
End if

--
Regards,
Tom Ogilvy

"TAM" wrote in message
. uk...
Can you please tell me if it is possible to return a value of say the
second
cell (B1) from the following statement...

Dim data As Variant

data = Range("A1:E1").Value

I have tried numerous ways but can not get the value of each cell in the
range which I need to examine before actioning

Thanks

TAM

Array Problem
 

TAM wrote:
Can you please tell me if it is possible to return a value of say the second
cell (B1) from the following statement...

Dim data As Variant

data = Range("A1:E1").Value

I have tried numerous ways but can not get the value of each cell in the
range which I need to examine before actioning

Thanks

TAM


Not exactly sure what your needs are, but after

Set rng = Range("A1:E1")

then

dataN = rng(N).Value will return for dataN the value from the Nth cell;
i.e.,

data1 = rng(1).Value will assign the value of A1 to data1;
data2 = rng(2).Value will assign the value of B1 to data2: etc.

Alan Beban

Array Problem
 

look, you dim 'data' as variant, then you try to give it value..i believe
you'd try:

redim data(1 to 5)

before you assign value..otherwise vba will show 'out of range' erro.

another error is, unless you dim data as range, i believe you need to loop
thorugh each data(i) to assign value. for example:

redim data(1 to 5)
for i = 1 to ubound(data)
xxxx
next

or dim data as range
set data = your range

rgds,

"TAM" wrote in message
. uk...
Can you please tell me if it is possible to return a value of say the
second
cell (B1) from the following statement...

Dim data As Variant

data = Range("A1:E1").Value

I have tried numerous ways but can not get the value of each cell in the
range which I need to examine before actioning

Thanks

TAM

Array Problem
 

Dim data As Variant
data = Range("A1:E1").Value

assigns to the variant variable "data" the array of values from A1:E1.

data(1,2) would then return the value that's in B1.

sbcglobal seems not to have tested the advice given.

Alan Beban

sbcglobal wrote:
look, you dim 'data' as variant, then you try to give it value..i believe
you'd try:

redim data(1 to 5)

before you assign value..otherwise vba will show 'out of range' erro.

another error is, unless you dim data as range, i believe you need to loop
thorugh each data(i) to assign value. for example:

redim data(1 to 5)
for i = 1 to ubound(data)
xxxx
next

or dim data as range
set data = your range

rgds,

"TAM" wrote in message
. uk...

Can you please tell me if it is possible to return a value of say the
second
cell (B1) from the following statement...

Dim data As Variant

data = Range("A1:E1").Value

I have tried numerous ways but can not get the value of each cell in the
range which I need to examine before actioning

Thanks

TAM