Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble using VLookup in VBA
Hi Everyone,
MS Excel 2003 SP3: VLookup is on the list of functions available listed by Help, but the folowing code generates the error "Sub or Function not defined". If IsNA(VLookup(sT, "WasinoParts", 1, True)) = False Then IsWONPart = True End If The blue light is on "VLookup". Seems it is OK with IsNA. I am thinking I need to load a reference library but can anyone tell me which one? The list is long and bewildering. The ones I have loaded are as follows: Visual Basic for Applications Microsoft Excel 11.0 Object Library OLE Automation Microsoft Office 11.0 Object Library Your help would be very much appreciated! Thank You, -plh |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble using VLookup in VBA
Hi,
I figured it out: With Application.WorksheetFunction If .IsNA(.VLookup(sT, "WasinoParts", 1, True)) = False Then IsWONPart = True End If End With Thanx, -plh On Jul 22, 10:08*am, plh wrote: Hi Everyone, MS Excel 2003 SP3: VLookup is on the list of functions available listed by Help, but the folowing code generates the error "Sub or Function not defined". * * If IsNA(VLookup(sT, "WasinoParts", 1, True)) = False Then * * * *IsWONPart = True * * End If The blue light is on "VLookup". Seems it is OK with IsNA. I am thinking I need to load a reference library but can *anyone tell me which one? The list is long and bewildering. The ones I have loaded are as follows: Visual Basic for Applications Microsoft Excel 11.0 Object Library OLE Automation Microsoft Office 11.0 *Object Library Your help would be very much appreciated! Thank You, -plh |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble using VLookup in VBA
Actually I am not out of the woods yet. I am getting RT error 1004
"Unable to get the VLookup property of the WorksheetFunction class" Can anyone help me with this problem? Thanks, -plh On Jul 22, 11:28*am, plh wrote: Hi, I figured it out: * * With Application.WorksheetFunction * * * * If .IsNA(.VLookup(sT, "WasinoParts", 1, True)) = False Then * * * * * *IsWONPart = True * * * * End If * * End With Thanx, -plh On Jul 22, 10:08*am, plh wrote: Hi Everyone, MS Excel 2003 SP3: VLookup is on the list of functions available listed by Help, but the folowing code generates the error "Sub or Function not defined". * * If IsNA(VLookup(sT, "WasinoParts", 1, True)) = False Then * * * *IsWONPart = True * * End If The blue light is on "VLookup". Seems it is OK with IsNA. I am thinking I need to load a reference library but can *anyone tell me which one? The list is long and bewildering. The ones I have loaded are as follows: Visual Basic for Applications Microsoft Excel 11.0 Object Library OLE Automation Microsoft Office 11.0 *Object Library Your help would be very much appreciated! Thank You, -plh- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble using VLookup in VBA
OK, I'll bite: I was able to solve that one by searchin on this group
and the Internet at large. I'll have to be less quick to post next time I run into a problem. -plh On Jul 22, 11:37*am, plh wrote: Actually I am not out of the woods yet. I am getting RT error 1004 "Unable to get the VLookup property of the WorksheetFunction class" Can anyone help me with this problem? Thanks, -plh On Jul 22, 11:28*am, plh wrote: Hi, I figured it out: * * With Application.WorksheetFunction * * * * If .IsNA(.VLookup(sT, "WasinoParts", 1, True)) = False Then * * * * * *IsWONPart = True * * * * End If * * End With Thanx, -plh On Jul 22, 10:08*am, plh wrote: Hi Everyone, MS Excel 2003 SP3: VLookup is on the list of functions available listed by Help, but the folowing code generates the error "Sub or Function not defined". * * If IsNA(VLookup(sT, "WasinoParts", 1, True)) = False Then * * * *IsWONPart = True * * End If The blue light is on "VLookup". Seems it is OK with IsNA. I am thinking I need to load a reference library but can *anyone tell me which one? The list is long and bewildering. The ones I have loaded are as follows: Visual Basic for Applications Microsoft Excel 11.0 Object Library OLE Automation Microsoft Office 11.0 *Object Library Your help would be very much appreciated! Thank You, -plh- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble using VLookup in VBA
Even though you found an answer, I like this technique:
Dim res as variant 'could return an error dim LookUpCol as range set lookupcol = worksheets("somesheetname").range("A1:A10") 'whatever?? res = application.match(sT, lookupcol, false) if iserror(res) then msgbox "not found" else msgbox "Found in row: " & res & " of the lookupcol" end if ======== A couple of things... I changed the function to =match() and I looked for an exact match. Since you're only looking to see if the value is in the list, I figured =match() was sufficient. But changing to look for an exact match may not be what you want! You can look at Excel's help to see what that third parm (-1, 0, 1) can do. But the biggest thing I did was to drop the .worksheetfunction from the code. There's a difference between how application.vlookup() and application.vlookup() and application.match() and application.worksheetfunction.match() works. The first returns an error that can be checked -- that's why I like it. The second causes a runtime error that has to be avoided with "on error" code. On 07/22/2010 11:46, plh wrote: OK, I'll bite: I was able to solve that one by searchin on this group and the Internet at large. I'll have to be less quick to post next time I run into a problem. -plh On Jul 22, 11:37 am, wrote: Actually I am not out of the woods yet. I am getting RT error 1004 "Unable to get the VLookup property of the WorksheetFunction class" Can anyone help me with this problem? Thanks, -plh On Jul 22, 11:28 am, wrote: Hi, I figured it out: With Application.WorksheetFunction If .IsNA(.VLookup(sT, "WasinoParts", 1, True)) = False Then IsWONPart = True End If End With Thanx, -plh On Jul 22, 10:08 am, wrote: Hi Everyone, MS Excel 2003 SP3: VLookup is on the list of functions available listed by Help, but the folowing code generates the error "Sub or Function not defined". If IsNA(VLookup(sT, "WasinoParts", 1, True)) = False Then IsWONPart = True End If The blue light is on "VLookup". Seems it is OK with IsNA. I am thinking I need to load a reference library but can anyone tell me which one? The list is long and bewildering. The ones I have loaded are as follows: Visual Basic for Applications Microsoft Excel 11.0 Object Library OLE Automation Microsoft Office 11.0 Object Library Your help would be very much appreciated! Thank You, -plh- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with Vlookup | Excel Discussion (Misc queries) | |||
VLOOKUP Trouble | Excel Programming | |||
Trouble with vLookup | New Users to Excel | |||
VLOOKUP trouble .. HELP! | Excel Worksheet Functions | |||
VLookup with VBA trouble :-( | Excel Programming |