using VLookUp in a VBA program
 

Hi all,

I am trying to use VLookUp in VBA. My code is as follows

Dim WorkSheet As String
Dim LookUpValue As String
Dim TableArray As String
Dim ColumnIndexNumber As String

WorkSheet = "'Links (S)'"
LookUpValue = CStr(ShiftNumber)
TableArray = WorkSheet & "!" & "$E$1:$G$70"
ColumnIndexNumber = "1"

ShiftType = Application.WorksheetFunction.VLookup(LookUpValue
TableArray, ColumnIndexNumber)

When I run it, it says that it can't find VLookUp as a
WorksheetFunction. I am not sure how I can make it work.

I have also tried Excel.WorksheetFunction.VLookup without success.

Another sub-question is, when i use EXCEL WORKSHEET FUNCTIONS, I assum
that all arguements to the FUNCTIONS are supplied as STRINGS. Is this
correct assumption?



Jame

--
Message posted from http://www.ExcelForum.com

using VLookUp in a VBA program
 

Do you have the AddIns selected Tools AddIns. I believe
that the XLookup functions are a part of the Analysis
addins. That could be why it can't find the function.


-----Original Message-----
Hi all,

I am trying to use VLookUp in VBA. My code is as follows

Dim WorkSheet As String
Dim LookUpValue As String
Dim TableArray As String
Dim ColumnIndexNumber As String

WorkSheet = "'Links (S)'"
LookUpValue = CStr(ShiftNumber)
TableArray = WorkSheet & "!" & "$E$1:$G$70"
ColumnIndexNumber = "1"

ShiftType = Application.WorksheetFunction.VLookup
(LookUpValue,
TableArray, ColumnIndexNumber)

When I run it, it says that it can't find VLookUp as an
WorksheetFunction. I am not sure how I can make it work.

I have also tried Excel.WorksheetFunction.VLookup without
success.

Another sub-question is, when i use EXCEL WORKSHEET
FUNCTIONS, I assume
that all arguements to the FUNCTIONS are supplied as
STRINGS. Is this a
correct assumption?



James


---
Message posted from http://www.ExcelForum.com/

.

using VLookUp in a VBA program
 

nano,
Does VLookUp work as expect when used on the worksheet ?

For VLookUp to return the correct value, the table has to be sorted.
Depending on the format of your "ShiftNumber", it may be appropriate to
regard the value as numeric or strings. If you sort 1,2,3,4,23,33,44 and
strings, you will get a different ordering to their numeric value.

Also, shouldn't ColumnIndexNumber be 1, as that's the left most column in
which you are doing your lookup.

Nick

"nano_electronix " wrote in
message ...
Hi all,

I am trying to use VLookUp in VBA. My code is as follows

Dim WorkSheet As String
Dim LookUpValue As String
Dim TableArray As String
Dim ColumnIndexNumber As String

WorkSheet = "'Links (S)'"
LookUpValue = CStr(ShiftNumber)
TableArray = WorkSheet & "!" & "$E$1:$G$70"
ColumnIndexNumber = "1"

ShiftType = Application.WorksheetFunction.VLookup(LookUpValue,
TableArray, ColumnIndexNumber)

When I run it, it says that it can't find VLookUp as an
WorksheetFunction. I am not sure how I can make it work.

I have also tried Excel.WorksheetFunction.VLookup without success.

Another sub-question is, when i use EXCEL WORKSHEET FUNCTIONS, I assume
that all arguements to the FUNCTIONS are supplied as STRINGS. Is this a
correct assumption?



James


---
Message posted from http://www.ExcelForum.com/

using VLookUp in a VBA program
 

Try application.Vlookup(value, range reference, column number, true or
false)

no, arguments are not passed as strings (see above).

--
Regards,
Tom Ogilvy

"nano_electronix " wrote in
message ...
Hi all,

I am trying to use VLookUp in VBA. My code is as follows

Dim WorkSheet As String
Dim LookUpValue As String
Dim TableArray As String
Dim ColumnIndexNumber As String

WorkSheet = "'Links (S)'"
LookUpValue = CStr(ShiftNumber)
TableArray = WorkSheet & "!" & "$E$1:$G$70"
ColumnIndexNumber = "1"

ShiftType = Application.WorksheetFunction.VLookup(LookUpValue,
TableArray, ColumnIndexNumber)

When I run it, it says that it can't find VLookUp as an
WorksheetFunction. I am not sure how I can make it work.

I have also tried Excel.WorksheetFunction.VLookup without success.

Another sub-question is, when i use EXCEL WORKSHEET FUNCTIONS, I assume
that all arguements to the FUNCTIONS are supplied as STRINGS. Is this a
correct assumption?



James


---
Message posted from http://www.ExcelForum.com/

using VLookUp in a VBA program
 

First of all, thank you all for your reply. Please read on.



Yes VLookup works fine as a worksheet formula, but i was thinking o
saving some coding by reusing the existing excel functions.




I've tried the following as suggested by Tom

Dim TempString As String
TempString = Application.VLookup("201", Me.Range("A1:F20"), 4
True)
MsgBox (TempString)

Application.VLookup is not detected by intellisense (so it's probabl
not the correct reference to the Vlookup function) and when I execute
the statement it returns with "Type Mismatch Error".




I have also tried the - Tools - Add-in and selected all the package
as suggested by Barry.

It returned the same error. "Unabled to get the Vlookup property of th
worksheetfunction class. I would have thought that I am suppose t
include the reference from VBE so that my program can reference th
Analysis Tool, but there are so many packages i am not sure which t
include for the worksheet function.

I have tried including the following 2 packages in the reference
1. atpvbaen.xls
2. funcres
But it still returns with the same error.




James




Tom Ogilvy wrote:
*Try application.Vlookup(value, range reference, column number, tru
or
false)

no, arguments are not passed as strings (see above).

--
Regards,
Tom Ogilvy

"nano_electronix "
wrote in
message ...
Hi all,

I am trying to use VLookUp in VBA. My code is as follows

Dim WorkSheet As String
Dim LookUpValue As String
Dim TableArray As String
Dim ColumnIndexNumber As String

WorkSheet = "'Links (S)'"
LookUpValue = CStr(ShiftNumber)
TableArray = WorkSheet & "!" & "$E$1:$G$70"
ColumnIndexNumber = "1"

ShiftType = Application.WorksheetFunction.VLookup(LookUpValue,
TableArray, ColumnIndexNumber)

When I run it, it says that it can't find VLookUp as an
WorksheetFunction. I am not sure how I can make it work.

I have also tried Excel.WorksheetFunction.VLookup without success.

Another sub-question is, when i use EXCEL WORKSHEET FUNCTIONS,
assume
that all arguements to the FUNCTIONS are supplied as STRINGS. I
this a
correct assumption?



James


---
Message posted from http://www.ExcelForum.com/


--
Message posted from http://www.ExcelForum.com

using VLookUp in a VBA program
 

I would really like to know how to use the existing excel workshee
functions in my VBA code. I have tried something simple like SUM whic
works, but I would like something like VLookUp to work. Any help i
greatly appreciated.

In the mean while I have made my own version of VLookUp which i
surprisingly simple. I didn't make it generic in anysense but made i
so that it suited my purpose.


Cheers
James :

--
Message posted from http://www.ExcelForum.com

using VLookUp in a VBA program
 

It's a Typo. I'm sure Tom meant:
WorksheetFunction.VLookup


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"nano_electronix " wrote in
message ...
First of all, thank you all for your reply. Please read on.



Yes VLookup works fine as a worksheet formula, but i was thinking of
saving some coding by reusing the existing excel functions.




I've tried the following as suggested by Tom

Dim TempString As String
TempString = Application.VLookup("201", Me.Range("A1:F20"), 4,
True)
MsgBox (TempString)

Application.VLookup is not detected by intellisense (so it's probably
not the correct reference to the Vlookup function) and when I executed
the statement it returns with "Type Mismatch Error".




I have also tried the - Tools - Add-in and selected all the packages
as suggested by Barry.

It returned the same error. "Unabled to get the Vlookup property of the
worksheetfunction class. I would have thought that I am suppose to
include the reference from VBE so that my program can reference the
Analysis Tool, but there are so many packages i am not sure which to
include for the worksheet function.

I have tried including the following 2 packages in the reference
1. atpvbaen.xls
2. funcres
But it still returns with the same error.




James




Tom Ogilvy wrote:
*Try application.Vlookup(value, range reference, column number, true
or
false)

no, arguments are not passed as strings (see above).

--
Regards,
Tom Ogilvy

"nano_electronix "
wrote in
message ...
Hi all,

I am trying to use VLookUp in VBA. My code is as follows

Dim WorkSheet As String
Dim LookUpValue As String
Dim TableArray As String
Dim ColumnIndexNumber As String

WorkSheet = "'Links (S)'"
LookUpValue = CStr(ShiftNumber)
TableArray = WorkSheet & "!" & "$E$1:$G$70"
ColumnIndexNumber = "1"

ShiftType = Application.WorksheetFunction.VLookup(LookUpValue,
TableArray, ColumnIndexNumber)

When I run it, it says that it can't find VLookUp as an
WorksheetFunction. I am not sure how I can make it work.

I have also tried Excel.WorksheetFunction.VLookup without success.

Another sub-question is, when i use EXCEL WORKSHEET FUNCTIONS, I
assume
that all arguements to the FUNCTIONS are supplied as STRINGS. Is
this a
correct assumption?



James


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/

using VLookUp in a VBA program
 

Actually, in several versions of XL (at least XL97, all MacXL versions
and, IIRC, XL00) VLookup was not implemented correctly into the
WorksheetFunctions collection, and WorksheetFunction.VLookup does not
work. Using the older Application.VLookup is still supported and works
in all versions.

In article ,
"Rob van Gelder" wrote:

It's a Typo. I'm sure Tom meant:
WorksheetFunction.VLookup

using VLookUp in a VBA program
 

Hi James,

A couple of points. VLookup is a built-in Excel function, so you do not
need to reference anything in order to use it. Second, all of the worksheet
functions available to VBA can be used directly off the Application object,
however, you will not get an intellisense list when you do this. That's
normal.

You will get an intellisense list if you prefix your worksheet function
names with Application.WorksheetFunction, but if you use the lookup
functions like this they will throw a run-time error if they do not find
what they are looking for. This is very often not what you want, and in fact
I believe it is what you are experiencing here. Try the following
modification to your code:

Dim rngLookup As Range
Dim vTemp As Variant
Set rngLookup = Me.Range("A1:F20")
vTemp = Application.VLookup("201", rngLookup, 4, True)
MsgBox CStr(vTemp)

Note that the Me keyword in the third line is only valid if this code is
being run from the code module behind the worksheet where the specified
lookup range is located. If you are running this from a normal code module
this line should be something like the following instead:

Set rngLookup = Sheet1.Range("A1:F20")

where Sheet1 is the CodeName of the worksheet the lookup range is located
on.

If the message box returns something like "Error 2042" it means that the
value 201 was not located in the first column of the specified range. Also,
setting the last argument of the VLookup function to True means that the
lookup range must be sorted by the first column in ascending order. If this
is not the case, substitute False instead.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"nano_electronix " wrote in
message ...
First of all, thank you all for your reply. Please read on.



Yes VLookup works fine as a worksheet formula, but i was thinking of
saving some coding by reusing the existing excel functions.




I've tried the following as suggested by Tom

Dim TempString As String
TempString = Application.VLookup("201", Me.Range("A1:F20"), 4,
True)
MsgBox (TempString)

Application.VLookup is not detected by intellisense (so it's probably
not the correct reference to the Vlookup function) and when I executed
the statement it returns with "Type Mismatch Error".




I have also tried the - Tools - Add-in and selected all the packages
as suggested by Barry.

It returned the same error. "Unabled to get the Vlookup property of the
worksheetfunction class. I would have thought that I am suppose to
include the reference from VBE so that my program can reference the
Analysis Tool, but there are so many packages i am not sure which to
include for the worksheet function.

I have tried including the following 2 packages in the reference
1. atpvbaen.xls
2. funcres
But it still returns with the same error.




James




Tom Ogilvy wrote:
*Try application.Vlookup(value, range reference, column number, true
or
false)

no, arguments are not passed as strings (see above).

--
Regards,
Tom Ogilvy

"nano_electronix "
wrote in
message ...
Hi all,

I am trying to use VLookUp in VBA. My code is as follows

Dim WorkSheet As String
Dim LookUpValue As String
Dim TableArray As String
Dim ColumnIndexNumber As String

WorkSheet = "'Links (S)'"
LookUpValue = CStr(ShiftNumber)
TableArray = WorkSheet & "!" & "$E$1:$G$70"
ColumnIndexNumber = "1"

ShiftType = Application.WorksheetFunction.VLookup(LookUpValue,
TableArray, ColumnIndexNumber)

When I run it, it says that it can't find VLookUp as an
WorksheetFunction. I am not sure how I can make it work.

I have also tried Excel.WorksheetFunction.VLookup without success.

Another sub-question is, when i use EXCEL WORKSHEET FUNCTIONS, I
assume
that all arguements to the FUNCTIONS are supplied as STRINGS. Is
this a
correct assumption?



James


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/

using VLookUp in a VBA program
 

Wow, Application.VLookup *does* work!

Apologies to Tom for the incorrect assumption.


That's worth knowing about - thanks.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"JE McGimpsey" wrote in message
...
Actually, in several versions of XL (at least XL97, all MacXL versions
and, IIRC, XL00) VLookup was not implemented correctly into the
WorksheetFunctions collection, and WorksheetFunction.VLookup does not
work. Using the older Application.VLookup is still supported and works
in all versions.

In article ,
"Rob van Gelder" wrote:

It's a Typo. I'm sure Tom meant:
WorksheetFunction.VLookup

using VLookUp in a VBA program
 

See Rob Bovey's post for a complete answer, but your problem is dim 'ng
TempString as String, If no match is found, Vlookup returns an error - a
string can't hold an error so dim TempString as Variant

Dim TempString As Variant
TempString = Application.VLookup("201", _
Me.Range("A1:F20"), 4,True)
if not iserror(TempString) then
MsgBox TempString
Else
msgbox "201 not found"
End if

--
Regards,
Tom Ogilvy

nano_electronix wrote in
message ...
First of all, thank you all for your reply. Please read on.



Yes VLookup works fine as a worksheet formula, but i was thinking of
saving some coding by reusing the existing excel functions.




I've tried the following as suggested by Tom

Dim TempString As String
TempString = Application.VLookup("201", Me.Range("A1:F20"), 4,
True)
MsgBox (TempString)

Application.VLookup is not detected by intellisense (so it's probably
not the correct reference to the Vlookup function) and when I executed
the statement it returns with "Type Mismatch Error".




I have also tried the - Tools - Add-in and selected all the packages
as suggested by Barry.

It returned the same error. "Unabled to get the Vlookup property of the
worksheetfunction class. I would have thought that I am suppose to
include the reference from VBE so that my program can reference the
Analysis Tool, but there are so many packages i am not sure which to
include for the worksheet function.

I have tried including the following 2 packages in the reference
1. atpvbaen.xls
2. funcres
But it still returns with the same error.




James




Tom Ogilvy wrote:
*Try application.Vlookup(value, range reference, column number, true
or
false)

no, arguments are not passed as strings (see above).

--
Regards,
Tom Ogilvy

"nano_electronix "
wrote in
message ...
Hi all,

I am trying to use VLookUp in VBA. My code is as follows

Dim WorkSheet As String
Dim LookUpValue As String
Dim TableArray As String
Dim ColumnIndexNumber As String

WorkSheet = "'Links (S)'"
LookUpValue = CStr(ShiftNumber)
TableArray = WorkSheet & "!" & "$E$1:$G$70"
ColumnIndexNumber = "1"

ShiftType = Application.WorksheetFunction.VLookup(LookUpValue,
TableArray, ColumnIndexNumber)

When I run it, it says that it can't find VLookUp as an
WorksheetFunction. I am not sure how I can make it work.

I have also tried Excel.WorksheetFunction.VLookup without success.

Another sub-question is, when i use EXCEL WORKSHEET FUNCTIONS, I
assume
that all arguements to the FUNCTIONS are supplied as STRINGS. Is
this a
correct assumption?



James


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/

using VLookUp in a VBA program
 

Special thanx to Rob Bovey and Tom Ogilvy for solving my problem. Thanks
to everyone else for contributing. You guys are superb. Now I know how
to use Excel built in funcitions : )

This is the first time that I have tried to writing any code in VB6 for
a VBA application. I started out a .Net programmer and found it really
hard to adjust to VB6 because it's not strongly typed, and it's not
strongly object oriented.

I hope that microsoft will soon release an office suite based on .NET
rather than the older platform. I have had a chance to use office 2003
and I was hoping there would be a new VBA.NET platform but it was not
to be. Let's hope .. .. .


Cheers and thanks again

James : )


---
Message posted from http://www.ExcelForum.com/

using VLookUp in a VBA program
 

While I was testing

Dim rngLookUp As Range
Dim vTemp As Variant
Set rngLookUp = Me.Range("C1:F12")
vTemp = Application.VLookup("201", rngLookUp, 3, False)
If IsError(vTemp) Then
MsgBox "Error: " & CStr(vTemp)
Else
MsgBox CStr(vTemp)
End If

I found that the above code snippet resulted in the 2042 (Data no
found) Error, But the following code snippet resulted in a match.

Dim rngLookUp As Range
Dim vTemp As Variant
Set rngLookUp = Me.Range("C1:F12")
vTemp = Application.VLookup(201, rngLookUp, 3, False)
If IsError(vTemp) Then
MsgBox "Error: " & CStr(vTemp)
Else
MsgBox CStr(vTemp)
End If

The only difference between the 2 code snippets is that the workin
snippet uses an number instead of a string representation of th
number.

I have had a perception that Excel handles the numeric to string an
string to numeric conversion automatically behind the scene, but thi
is seemingly untrue for this particular case. I guess it's dependant o
the VLOOKUP implementation.

Just thought it might be worth noting for other VBA noobies like me. I
is an assumption that we should all avoid.

James :

--
Message posted from http://www.ExcelForum.com

using VLookUp in a VBA program
 

VBA does handle the conversion when warranted - however, vlookup could look
for a string or a number, so there is no reason to perform a conversion.
Also, vlookup matches to the same. If looking for the string "201", then a
match is only found if there is a string "201" in the left column of the
lookup range; likewise for the number 201, only if there is a number 201 in
the left column of the lookup range. This is the same behavior as in the
worksheet.


--
Regards,
Tom Ogilvy


"nano_electronix " wrote in
message ...
While I was testing

Dim rngLookUp As Range
Dim vTemp As Variant
Set rngLookUp = Me.Range("C1:F12")
vTemp = Application.VLookup("201", rngLookUp, 3, False)
If IsError(vTemp) Then
MsgBox "Error: " & CStr(vTemp)
Else
MsgBox CStr(vTemp)
End If

I found that the above code snippet resulted in the 2042 (Data not
found) Error, But the following code snippet resulted in a match.

Dim rngLookUp As Range
Dim vTemp As Variant
Set rngLookUp = Me.Range("C1:F12")
vTemp = Application.VLookup(201, rngLookUp, 3, False)
If IsError(vTemp) Then
MsgBox "Error: " & CStr(vTemp)
Else
MsgBox CStr(vTemp)
End If

The only difference between the 2 code snippets is that the working
snippet uses an number instead of a string representation of the
number.

I have had a perception that Excel handles the numeric to string and
string to numeric conversion automatically behind the scene, but this
is seemingly untrue for this particular case. I guess it's dependant on
the VLOOKUP implementation.

Just thought it might be worth noting for other VBA noobies like me. It
is an assumption that we should all avoid.

James :)


---
Message posted from http://www.ExcelForum.com/

using VLookUp in a VBA program
 

This might come as as silly question to ask, but how do we determine i
a cell is of string type or numeric type?

From a programmer's point of view, the user is given an interface t
enter their values, this interface is the cells in excel. When the use
enters a value into the box, excel determines the type of value it i
and automatically formats the screen so that numbers are right aligne
where as strings are left aligned. When this happens does it mean tha
the cell value is given a type by excel? Whether it be string type o
numeric type.

James : )

Tom Ogilvy wrote:
*VBA does handle the conversion when warranted - however, vlooku
could look
for a string or a number, so there is no reason to perform
conversion.
Also, vlookup matches to the same. If looking for the string "201"
then a
match is only found if there is a string "201" in the left column o
the
lookup range; likewise for the number 201, only if there is a numbe
201 in
the left column of the lookup range. This is the same behavior as i
the
worksheet.



--
Message posted from http://www.ExcelForum.com

using VLookUp in a VBA program
 

The left/right alignment is not something you can hang your hat on.

If the user left justifies the cell, then both text and numbers will be left
justified.

In a worksheet cell, you can
=isnumber(a1)
or
=istext(a1)

In VBA, you can use:
If Application.IsNumber(Range("a1").Value) Then
or
If TypeName(Range("a1").Value) = "Double" Then
or
If VarType(Range("a1").Value) = vbDouble Then


"nano_electronix <" wrote:

This might come as as silly question to ask, but how do we determine if
a cell is of string type or numeric type?

From a programmer's point of view, the user is given an interface to
enter their values, this interface is the cells in excel. When the user
enters a value into the box, excel determines the type of value it is
and automatically formats the screen so that numbers are right aligned
where as strings are left aligned. When this happens does it mean that
the cell value is given a type by excel? Whether it be string type or
numeric type.

James : )

Tom Ogilvy wrote:
*VBA does handle the conversion when warranted - however, vlookup
could look
for a string or a number, so there is no reason to perform a
conversion.
Also, vlookup matches to the same. If looking for the string "201",
then a
match is only found if there is a string "201" in the left column of
the
lookup range; likewise for the number 201, only if there is a number
201 in
the left column of the lookup range. This is the same behavior as in
the
worksheet.

*

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

using VLookUp in a VBA program
 

Hello guys, I found this thread (and forum) by looking up the meaning o
error 2042. Thanks to you guys now I know that the value looked up i
not found in the range. And there it lies the problem!

I have a very large list from which the values can be looked up, so,
created a small VBA function that breaks down the list in smalle
ranges and then using a Select statement it looks up a value within
range.

If I put in Excel the following, the answer is TRUE:
= "EP102RB" < "EP-1080SD"

If I open an immediate window in VBA, the following returns FALSE:
PRINT "EP102RB" < "EP-1080SD"

How can that be? BTW, I never saw this happenign in 95, 97, 2K

--
Message posted from http://www.ExcelForum.com

using VLookUp in a VBA program
 

Thanks to this thread I solved as well my problem with vlookup,
already started to bang my head against the wall.

so definitely "Application.vlookup" is the solution.

Problem: checking a list of unique values, to determine if a "ne
value" to be added is really "new".
If the value exists in the list I check, then the information to th
right of the value is a "String"

Solution:

Code
-------------------

MyResult = Application.VLookup(MyValue, Range(MyRange), 2, False)

Select Case VarType(MyResult)

Case vbError
Select Case MyResult
Case CVErr(2042) '"Data not found"
'This is the desired condition in my solution

Case Else
'Another error occured that I dont know about
MsgBox CStr(MyResult), , "vlookup"
Exit Sub
End Select

Case vbString ' The Value was found, meaning my value is not unique.
Exit Sub

Case Else 'another vartype was returned that I did not expect
MsgBox VarType(Result), , "vlookup"
Exit Sub

End Select

-------------------


--
Message posted from http://www.ExcelForum.com