Re: Using a 2 dimensional string array variable like a lookup function
- From: "Conan Kelly" <CTBarbarinNOSPAM@xxxxxxxxxxxxxxxxxxx>
- Date: Thu, 29 Nov 2007 21:12:05 GMT
Chip,
Thanks for info......much appreciated.
Thanks again,
Conan
"Chip Pearson" <chip@xxxxxxxxxxxx> wrote in message
news:DDAA2E46-0E51-455F-B1CA-C37BC71D3A61@xxxxxxxxxxxxxxxx
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 Consulting
www.cpearson.com
(email on the web site)
"Conan Kelly" <CTBarbarinNOSPAM@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
.
- References:
- Using a 2 dimensional string array variable like a lookup function
- From: Conan Kelly
- Re: Using a 2 dimensional string array variable like a lookup function
- From: Chip Pearson
- Using a 2 dimensional string array variable like a lookup function
- Prev by Date: Re: Finding words with capital letters and numbers in them
- Next by Date: MS Query Application Window Remains Open After Refreshing PivotTable
- Previous by thread: Re: Using a 2 dimensional string array variable like a lookup function
- Next by thread: Re: Using a 2 dimensional string array variable like a lookup function
- Index(es):
Relevant Pages
|
