Re: Using a 2 dimensional string array variable like a lookup function

Does this cause a cast from string array to a range? Or is
Application.Vlookup different from
Application.WorksheetFunction.Vlookup as far as argument types?


On Nov 29, 3:44 pm, "Chip Pearson" <c...@xxxxxxxxxxxx> wrote:
You can use VLOOKUP against an array in code. This would be much more
efficient than looping. For example,

Sub AAA()
Dim V As Variant
Dim Arr() As String
Dim LookupTerm As String

ReDim Arr(1 To 3, 1 To 2)
Arr(1, 1) = "term1"
Arr(1, 2) = "path1"
Arr(2, 1) = "term2"
Arr(2, 2) = "path2"
Arr(3, 1) = "term3"
Arr(3, 2) = "path3"

LookupTerm = "term1"
V = Application.VLookup(LookupTerm, Arr, 2)
If IsError(V) = True Then
Debug.Print "Term not found"
Else
Debug.Print "Path: " & V
End If
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consultingwww.cpearson.com
(email on the web site)

"Conan Kelly" <CTBarbarinNOS...@xxxxxxxxxxxxxxxxxxx> wrote in message

news:HLE3j.173046$kj1.106179@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



Hello all,

How would I use a 2 dimensional string array like a vlookup/hlookup?

I'm thinking that my array will be something like this:

dim pstrDestinationPath(0 to [n], 0 to 1) as string

so in this 2 x [n] array, I will put a search term (a file name will be
searched for this term) in the "first column" and the corresponding
destination path in the "second column" of the array.

what I'm thinking of right now is just using a For loop to loop through
each element of the "first column" of the array, comparing it to a string.
Then using the For Loop control counter (after the match is found) as the
index for the "2nd column" of the array to return the matching path
element.

Is this the correct way of doing this, or is there some other more
straight forward way?

Thanks for any help anyone can provide,

Conan Kelly- Hide quoted text -

- Show quoted text -

.

Quantcast