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/