Vlookup within an array

[beachie2037]

Hi,

I have an array of data thats being called in a macro. Is it possible to run a vlookup in the macro array based on a cell refence on the page. Basically I want to use the customer number to reference a cell of data (customer name) that will be on a row corresponding to the cell reference.

See below for example of what I WOULD LIKE TO DO

Dim EditCust(3)
EditCust(0) = Range("A1")
EditCust(1) = VLOOKUP(A1,CustomerDB!A5:D7,2,FALSE)
EditCust(2) = Range("A3")

Obviously this doesnt work - is it possible to do what I want to do?

ANy help appreciated.


Steve

 

[Yogi Anand]

Hi Steve:

instead of ...

VLOOKUP(A1,CustomerDB!A5:D7,2,FALSE)

try

evaluate("=VLOOKUP(A1,CustomerDB!A5:D7,2,FALSE)")

I hope this helps!

 

[acw]

Steve

try something like

editcust(1) = worksheetfunction.vlookup(range("a1"),sheets("customerDB").range("a5:d7"),2,false)

Tony

 

[Tazguy37]

Perhaps try:

EditCust(1) = Application.WorksheetFunction.VLookup(Range("A1").Value, Worksheets("CustomerDB").Range("A5:D7"), 2, 0)

Hope that helps!

EDIT: Both work. Perhaps someone will 'splain to us why you don't need the Application Object in this case. I don't know why.