VBA Question: how perform a "match" in a VBA array

[strattergize]

If I have a 2 dimensional VB array, can I perform the equivalent of a vlookup on it? The following code didn't work:

x = WorksheetFunction.VLookup("271", MasterArray, 2, 0)

Or if it is simpler, what code could determine where a given value is in a one-dimensional array? In Excel I would simply use the MATCH function and it would return what position the given value is. If I could know the position then I could just use code like this to retrieve the ultimate value:

x = MasterArray(determined_position_of_value, 2)

Any help would be greatly appreciated! Thanks in advance.

JaredSorensen

 

[JASONW10]

here is one way
Cells.Find(What:=UserForm1.txtPT, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate ' this looks in userform1.txtpt if match is found this is tells userform1.txtpt = activecell that it found

UserForm1.txtPT = ActiveCell 'cell it finds

 

[Richard Schollar]

Hi Jared & Welcome to the Board!

You can use Match on a an array that only has the one dimension therefore the following will determine the position in the array of the sought for string:

Dim vArr as Variant
vArr = Range("A1:A10").Value 'fill variant array with cell values in A1:A10

Msgbox Application.WorksheetFunction.Match("SomeValue",vArr,0)  'this will return the position of "SomeValue" within vArr or an error if it fails to find it

With a two dimensional array you can use Vlookup eg:

Dim vArr2 as Variant
vArr2 = Range("A1:B10").Value 

Msgbox Application.WorksheetFunction.Vlookup("SomeValue",vArr2,2,0)

Make sense?

 

[strattergize]

Worked like a charm--thanks! I had wasted a lot of time trying to figure it out.

JaredSorensen

 

[excel_solutions]

hi,

I want a formula which would do the following:
Matching the date (column B) with that in row H2 onwards, and then it will match the names written in column C with names in column H
and after matching these conditions, the hyperlink will connect to name in column H

please help!!!!