vlookup type mismatch error

[Lilivati]

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!

 

[Tom Ogilvy]

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.

 

[NickHK]

Isn't .VLOOKUP a worksheetfunction ?
oXL.WorksheetFunction.VLookup(pnum, wsrange, 2, False)

NickHK

 

[ViestaWu]

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