Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup type mismatch error
Hello again,
I'm having an exceptionally persistent and rather annoying vlookup problem. I use this function a number of times in various macros, and it seems random whether on any given day it will decide to work or not. A macro that worked fine yesterday may fail to work today, without me making any changes to the script or documents involved. The problem is "runtime error 13: type mismatch". I have checked the type of both the element in and the element out (avoiding the vlookup function) and found them to be identical, so I have *no* idea where this type mismatch is coming from. The example below involves a table in word simply because that's what I'm working on right now, but I have encountered this error at some point every single time I have used vlookup. Sub tablefun() Dim oXL As Excel.Application Set oXL = GetObject(, "Excel.Application") Dim oXLwb As Excel.Workbook Set oXLwb = oXL.Workbooks("formtest.xls") Dim pnum As Variant pnum = Selection.Cells(1).Range.Text 'testing the type of pnum, it returns as a string 'MsgBox TypeName(pnum) Dim pname As Variant Dim wsrange As Range 'testing the type of pname (since if vlookup worked it would find the value in A1 and return the value in A2) 'pname = oXLwb.Worksheets(1).Range("A1").Value 'Dim pname2 As Variant 'pname2 = oXLwb.Worksheets(1).Range("A2").Value 'MsgBox TypeName(pname) 'MsgBox TypeName(pname2) Set wsrange = oXLwb.Worksheets(1).Range("A:B") 'here is where the type mismatch is thrown pname = oXL.VLookup(pnum, wsrange, 2, False) MsgBox pname End Sub As I said above, the type returns as string for each of the values involved, so I don't understand how vlookup is thinking their types don't match. I have tried wrapping the entire function as well as pnum in the function with CStr(), to no avail, as well as defining pnum and pname as strings rather than variants. I would badly like to get to the root of this problem, because I am having to deal with it on a daily basis, and constantly revisit "finished" work when it decides not to play nice today. I'm also open to a solution that uses a different method than vlookup if it will be more stable. I am using excel 2000 on win 2000 if that helps. Thanks a bunch! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup type mismatch error
Are you sure it is on the assignment statement. I would suggest
pname = oXL.VLookup(pnum, wsrange, 2, False) if iserror(pname) then msgbox pnum & " was not found" else MsgBox pname end sub I use the above construct all the time and have never had any problems. -- Regards, Tom Ogilvy "Lilivati" wrote: Hello again, I'm having an exceptionally persistent and rather annoying vlookup problem. I use this function a number of times in various macros, and it seems random whether on any given day it will decide to work or not. A macro that worked fine yesterday may fail to work today, without me making any changes to the script or documents involved. The problem is "runtime error 13: type mismatch". I have checked the type of both the element in and the element out (avoiding the vlookup function) and found them to be identical, so I have *no* idea where this type mismatch is coming from. The example below involves a table in word simply because that's what I'm working on right now, but I have encountered this error at some point every single time I have used vlookup. Sub tablefun() Dim oXL As Excel.Application Set oXL = GetObject(, "Excel.Application") Dim oXLwb As Excel.Workbook Set oXLwb = oXL.Workbooks("formtest.xls") Dim pnum As Variant pnum = Selection.Cells(1).Range.Text 'testing the type of pnum, it returns as a string 'MsgBox TypeName(pnum) Dim pname As Variant Dim wsrange As Range 'testing the type of pname (since if vlookup worked it would find the value in A1 and return the value in A2) 'pname = oXLwb.Worksheets(1).Range("A1").Value 'Dim pname2 As Variant 'pname2 = oXLwb.Worksheets(1).Range("A2").Value 'MsgBox TypeName(pname) 'MsgBox TypeName(pname2) Set wsrange = oXLwb.Worksheets(1).Range("A:B") 'here is where the type mismatch is thrown pname = oXL.VLookup(pnum, wsrange, 2, False) MsgBox pname End Sub As I said above, the type returns as string for each of the values involved, so I don't understand how vlookup is thinking their types don't match. I have tried wrapping the entire function as well as pnum in the function with CStr(), to no avail, as well as defining pnum and pname as strings rather than variants. I would badly like to get to the root of this problem, because I am having to deal with it on a daily basis, and constantly revisit "finished" work when it decides not to play nice today. I'm also open to a solution that uses a different method than vlookup if it will be more stable. I am using excel 2000 on win 2000 if that helps. Thanks a bunch! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup type mismatch error
Isn't .VLOOKUP a worksheetfunction ?
oXL.WorksheetFunction.VLookup(pnum, wsrange, 2, False) NickHK "Lilivati" wrote in message oups.com... Hello again, I'm having an exceptionally persistent and rather annoying vlookup problem. I use this function a number of times in various macros, and it seems random whether on any given day it will decide to work or not. A macro that worked fine yesterday may fail to work today, without me making any changes to the script or documents involved. The problem is "runtime error 13: type mismatch". I have checked the type of both the element in and the element out (avoiding the vlookup function) and found them to be identical, so I have *no* idea where this type mismatch is coming from. The example below involves a table in word simply because that's what I'm working on right now, but I have encountered this error at some point every single time I have used vlookup. Sub tablefun() Dim oXL As Excel.Application Set oXL = GetObject(, "Excel.Application") Dim oXLwb As Excel.Workbook Set oXLwb = oXL.Workbooks("formtest.xls") Dim pnum As Variant pnum = Selection.Cells(1).Range.Text 'testing the type of pnum, it returns as a string 'MsgBox TypeName(pnum) Dim pname As Variant Dim wsrange As Range 'testing the type of pname (since if vlookup worked it would find the value in A1 and return the value in A2) 'pname = oXLwb.Worksheets(1).Range("A1").Value 'Dim pname2 As Variant 'pname2 = oXLwb.Worksheets(1).Range("A2").Value 'MsgBox TypeName(pname) 'MsgBox TypeName(pname2) Set wsrange = oXLwb.Worksheets(1).Range("A:B") 'here is where the type mismatch is thrown pname = oXL.VLookup(pnum, wsrange, 2, False) MsgBox pname End Sub As I said above, the type returns as string for each of the values involved, so I don't understand how vlookup is thinking their types don't match. I have tried wrapping the entire function as well as pnum in the function with CStr(), to no avail, as well as defining pnum and pname as strings rather than variants. I would badly like to get to the root of this problem, because I am having to deal with it on a daily basis, and constantly revisit "finished" work when it decides not to play nice today. I'm also open to a solution that uses a different method than vlookup if it will be more stable. I am using excel 2000 on win 2000 if that helps. Thanks a bunch! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup type mismatch error
Hello,
I'm always confused how to assign Function to cell via VBA. Can I do it with Application.WorkSheetFunction.Vlookup(...)? Then How to assign to Cell? Thanks At advance, Viesta Shanghai, CN "NickHK" wrote: Isn't .VLOOKUP a worksheetfunction ? oXL.WorksheetFunction.VLookup(pnum, wsrange, 2, False) NickHK "Lilivati" wrote in message oups.com... Hello again, I'm having an exceptionally persistent and rather annoying vlookup problem. I use this function a number of times in various macros, and it seems random whether on any given day it will decide to work or not. A macro that worked fine yesterday may fail to work today, without me making any changes to the script or documents involved. The problem is "runtime error 13: type mismatch". I have checked the type of both the element in and the element out (avoiding the vlookup function) and found them to be identical, so I have *no* idea where this type mismatch is coming from. The example below involves a table in word simply because that's what I'm working on right now, but I have encountered this error at some point every single time I have used vlookup. Sub tablefun() Dim oXL As Excel.Application Set oXL = GetObject(, "Excel.Application") Dim oXLwb As Excel.Workbook Set oXLwb = oXL.Workbooks("formtest.xls") Dim pnum As Variant pnum = Selection.Cells(1).Range.Text 'testing the type of pnum, it returns as a string 'MsgBox TypeName(pnum) Dim pname As Variant Dim wsrange As Range 'testing the type of pname (since if vlookup worked it would find the value in A1 and return the value in A2) 'pname = oXLwb.Worksheets(1).Range("A1").Value 'Dim pname2 As Variant 'pname2 = oXLwb.Worksheets(1).Range("A2").Value 'MsgBox TypeName(pname) 'MsgBox TypeName(pname2) Set wsrange = oXLwb.Worksheets(1).Range("A:B") 'here is where the type mismatch is thrown pname = oXL.VLookup(pnum, wsrange, 2, False) MsgBox pname End Sub As I said above, the type returns as string for each of the values involved, so I don't understand how vlookup is thinking their types don't match. I have tried wrapping the entire function as well as pnum in the function with CStr(), to no avail, as well as defining pnum and pname as strings rather than variants. I would badly like to get to the root of this problem, because I am having to deal with it on a daily basis, and constantly revisit "finished" work when it decides not to play nice today. I'm also open to a solution that uses a different method than vlookup if it will be more stable. I am using excel 2000 on win 2000 if that helps. Thanks a bunch! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting - Run Time Error '13' Type Mismatch Error | Excel Programming | |||
Type mismatch in vlookup | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Type Mismatch in Vlookup? | Excel Programming |