Using a 2 dimensional string array variable like a lookup function

[Conan Kelly]

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

 

[Chip Pearson]

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]

Chip,

Thanks for info......much appreciated.

Thanks again,

Conan

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)

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

 

[ilia]

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?

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)

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 -

 

[Chip Pearson]

VLOOKUP can use either an array or a Range. No casting is done. For
example, you can use an array in a worksheet formula that doesn't reference
any Range at all.

=VLOOKUP(3,{1,"a";2,"b";3,"c"},2,FALSE)

The arguments for any worksheet function callable from VBA (optionally using
WorksheetFunction) has the exact same arguments, and those arguments have
the same meaning, as if the function were called directly from a worksheet
cell.

See http://www.cpearson.com/excel/CallingWorksheetFunctionsInVba.aspx ,
especially the section on error handling.


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

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?

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)

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 -

 

[ilia]

Thanks Chip.

VLOOKUP can use either an array or a Range. No casting is done. For
example, you can use an array in a worksheet formula that doesn't reference
any Range at all.

=VLOOKUP(3,{1,"a";2,"b";3,"c"},2,FALSE)

The arguments for any worksheet function callable from VBA (optionally using
WorksheetFunction) has the exact same arguments, and those arguments have
the same meaning, as if the function were called directly from a worksheet
cell.

Seehttp://www.cpearson.com/excel/CallingWorksheetFunctionsInVba.aspx,
especially the section on error handling.

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

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?

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)

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 -- Hide quoted text -

- Show quoted text -