Lookup value in array

[tiredofit]

I have a table consisting of 2 columns, Date and Price.

This table is called MyTable.

For a given date, I want to perform a vlookup.

Dim TableArray() As Variant

TableArray = Range("MyTable")

In a class I have:

Private mintPrice As Single

Public Property Get Price() As Single

Price = Application.Vlookup(msngPrice, TableArray ,2, False)

How can I make this work?

Thanks

 

[Aaron Blood]

I think you'd have to do something in your code to declare a storage area for the variant array so the value/range/array persists as the declared variable after it is first loaded...

Something like this:

Public TableArray() As Variant

Sub InitVariant()
    TableArray = Range("MyTable")
End Sub

Sub MyLookupSub()
    MsgBox Application.VLookup(7, TableArray, 2, False)
End Sub

Whether you declare it Public (for usage in all other modules and classes) or Private (for use within the module/class alone) is a programming structure decision you can reason out and adjust for accordingly...