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

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" wrote in message
...
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,

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

Thanks again,

Conan




"Chip Pearson" wrote in message
...
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" wrote in message
...
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

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" 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" wrote in message

...



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 -

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/Callin...ionsInVba.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)


"ilia" wrote in message
...
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" 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" wrote in message

...



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 -

Thanks Chip.

On Nov 29, 6:24 pm, "Chip Pearson" wrote:
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)

"ilia" wrote in message

...



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" 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" wrote in message

...

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 -