I am atempting to reference a specific element in a one-diminsional
array. The array is created on a worksheet using the Bloomberg BLP
fuctionality. It essentially enters a number in each of five columns.
Previously, I have been cell referencing the desired item (the 5th)
from the returned array as it is the only item I want. This requires
that the array elements be on the worksheet.

I would rather have a user-defined function that returns just the 5th
item. Something like:

=ReturnedItem("Bloomberg Created Array",5)

Obviously the syntax isn't right but to illustrate.
Any suggestions?

GH

An array of cells in columns is two dimensional, even if it's only in one
column. Try:

MsgBox Range("b5:b14")(5, 1), , Range("b5:b14")(5, 1).Address

As there is only one column you could also use Range("b5:b14")(5) which
returns the 5th cell in the range.

But:
vArr = Range("b5:b14")
msgbox varr(5,1)

Regards,
Peter T


"GH" wrote in message
oups.com...
I am atempting to reference a specific element in a one-diminsional
array. The array is created on a worksheet using the Bloomberg BLP
fuctionality. It essentially enters a number in each of five columns.
Previously, I have been cell referencing the desired item (the 5th)
from the returned array as it is the only item I want. This requires
that the array elements be on the worksheet.

I would rather have a user-defined function that returns just the 5th
item. Something like:

=ReturnedItem("Bloomberg Created Array",5)

Obviously the syntax isn't right but to illustrate.
Any suggestions?

GH

An array of cells in columns is two dimensional, even if it's only in one
column. Try:

MsgBox Range("b5:b14")(5, 1), , Range("b5:b14")(5, 1).Address

As there is only one column you could also use Range("b5:b14")(5) which
returns the 5th cell in the range.

But:
vArr = Range("b5:b14")
msgbox varr(5,1)

Regards,
Peter T


"GH" wrote in message
oups.com...
I am atempting to reference a specific element in a one-diminsional
array. The array is created on a worksheet using the Bloomberg BLP
fuctionality. It essentially enters a number in each of five columns.
Previously, I have been cell referencing the desired item (the 5th)
from the returned array as it is the only item I want. This requires
that the array elements be on the worksheet.

I would rather have a user-defined function that returns just the 5th
item. Something like:

=ReturnedItem("Bloomberg Created Array",5)

Obviously the syntax isn't right but to illustrate.
Any suggestions?

GH

I gave a misleading reply having misread the post. I said:
An array of cells in columns is two dimensional, even if it's only in one
column.
but should have said:
An array of cells in rows is two dimensional, even if it's only in one
column

and doesn't directly relate to the question. Instead try

Sub test()
Dim vArr
MsgBox Range("a2:j2")(1, 5), , Range("a2:j2")(1, 5).Address
vArr = Range("a2:j2")
MsgBox vArr(1, 5)
End Sub

Regards,
Peter T

PS sorry my earlier double post, not aware of sending twice.


"Peter T" <peter_t@discussions wrote in message
...
An array of cells in columns is two dimensional, even if it's only in one
column. Try:

MsgBox Range("b5:b14")(5, 1), , Range("b5:b14")(5, 1).Address

As there is only one column you could also use Range("b5:b14")(5) which
returns the 5th cell in the range.

But:
vArr = Range("b5:b14")
msgbox varr(5,1)

Regards,
Peter T


"GH" wrote in message
oups.com...
I am atempting to reference a specific element in a one-diminsional
array. The array is created on a worksheet using the Bloomberg BLP
fuctionality. It essentially enters a number in each of five columns.
Previously, I have been cell referencing the desired item (the 5th)
from the returned array as it is the only item I want. This requires
that the array elements be on the worksheet.

I would rather have a user-defined function that returns just the 5th
item. Something like:

=ReturnedItem("Bloomberg Created Array",5)

Obviously the syntax isn't right but to illustrate.
Any suggestions?

GH

I gave a misleading reply having misread the post. I said:
An array of cells in columns is two dimensional, even if it's only in one
column.
but should have said:
An array of cells in rows is two dimensional, even if it's only in one
column

and doesn't directly relate to the question. Instead try

Sub test()
Dim vArr
MsgBox Range("a2:j2")(1, 5), , Range("a2:j2")(1, 5).Address
vArr = Range("a2:j2")
MsgBox vArr(1, 5)
End Sub

Regards,
Peter T

PS sorry my earlier double post, not aware of sending twice.


"Peter T" <peter_t@discussions wrote in message
...
An array of cells in columns is two dimensional, even if it's only in one
column. Try:

MsgBox Range("b5:b14")(5, 1), , Range("b5:b14")(5, 1).Address

As there is only one column you could also use Range("b5:b14")(5) which
returns the 5th cell in the range.

But:
vArr = Range("b5:b14")
msgbox varr(5,1)

Regards,
Peter T


"GH" wrote in message
oups.com...
I am atempting to reference a specific element in a one-diminsional
array. The array is created on a worksheet using the Bloomberg BLP
fuctionality. It essentially enters a number in each of five columns.
Previously, I have been cell referencing the desired item (the 5th)
from the returned array as it is the only item I want. This requires
that the array elements be on the worksheet.

I would rather have a user-defined function that returns just the 5th
item. Something like:

=ReturnedItem("Bloomberg Created Array",5)

Obviously the syntax isn't right but to illustrate.
Any suggestions?

GH