VBA: return an item from an array (without using a loop)

[artrookie]

Hi

Could someone please help me with my query? Basically, I am storing a line of data into an array and would like to know if there is coding that returns the n'th item in this array without using a loop?

Attached below is a simplied version of the coding.

Assume Range("a1:e1").value has values {1,2,3,4,5}

Sub Macro1()
a = Range("a1:e1").Value
Range("a2").value = a
End Sub

Is it possible to modify this code to return item 5 into cell "a2" without a loop?

Range("a2").value = a(4) produces an error

Thanks in advance

Arthur

[Bob Phillips]

Sub Macro1()
Dim a As Variant
a = Range("A1:E1").Cells(1, 5).Value
Range("A2").Value = a
End Sub

[artrookie]

Hi Xld

Thanks for the quick reply, I was hoping it would be possible to take the whole row of data in rather then just taking one value,

Sub Macro1()
a = Range("a1:e1").Value - This line unchanged
Range("a2").value = a(4) - Hoping to change this line without loop
End Sub

I have searched alot on google for a solution, most of them have a loop component to it. But, I remember I did find something on google, but lost the coding for it.

Any help much appreciated

Thanks

Arthur

[Andy Pope]

the returned array is a 2 dimensional array.


Range("a2").Value = a(1, 5)

[Bob Phillips]

Why, what does that approach give you as against what I showed you? Unless you want the data in an array for later processing it seems unnecessary to me.

[mdmackillop]

Range("A2") = Range("A1:E1")(5)

[mdmackillop]

If you need "a" for something else

Dim a As Range
Set a = Range("A1:E1")
Range("A2") = a(5)

[artrookie]

Wow thanks guys!! All the comments have been useful and works