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

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/

.

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/

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/

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

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

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/

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

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

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/

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/

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/

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

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