Vlookup?

[Seedubs]

Firstly, Hello all,

I have the following code which is activated on a command button to go
to another worksheet and look for a value (in the text box
txtloanMemNo1) and then return the value in the column to the right of
it and write that into another text box (txtloanmemName1). The data in
the other worksheet is set as a database, but I have two other sheets
with databases in them too? This code is erroring with:

Unable to get the VLookup property of the Worksheetfunction class.

Any ideas - is it looking in the wrong place - do I have to name the
database or sheet?


Private Sub Cmdbtnpop1_Click()
TxtloanMemName1.Value = WorksheetFunction.VLookup(txtloanMemNo1.Value,
database, 2)
End Sub

This code is part of a user form.

<edit> using XP Pro and Office 2000.

Any pointers would be appreciated.

 

[Frank Kabel]

Hi
if database is a defined name try
TxtloanMemName1.Value = WorksheetFunction.VLookup(txtloanMemNo1.Value,
Range("database"), 2,0)

 

[Dave Peterson]

If you use application.vlookup, you can return an error value.

If you use worksheetfunction.vlookup, any error needs to be trapped by your
code:

Private Sub Cmdbtnpop1_Click()
dim res as variant
res = application.vlookup(txtloanmemno1.value,database,2)

if iserror(res) then
txtloanmemname1.value = "missing"
else
txtloanMemName1.Value = res
end if
End Sub

Private Sub Cmdbtnpop1_Click()
dim res as variant
on error resume next
res = WorksheetFunction.VLookup(txtloanMemNo1.Value,database, 2)
if err.number <> 0 then
textloanmemname1.value = "missing"
else
textloandmemname1.value = res
end if
End Sub


I normally have my vlookup look for an exact match when working with text:

....vlookup(txtloanmemno1.value,database,2,false)

 

[Tom Ogilvy]

Private Sub Cmdbtnpop1_Click()
on Error Resume Next
res = WorksheetFunction. _
VLookup(txtloanMemNo1.Value,Worksheets("Sheet2"). _
Range("A1:B500"),2)
if err.number = 0 then
err.Clear
TxtloanMemName1.Value = res
End if
End Sub


If the value being searched for is not found, then the vlookup raises a
trappable error. So you have to account for this. Also, not sure what
"database" means in your procedure, but a valid range reference must be
used. If you have a defined name "database", you could use
Range("Database") but it wasn't clear that is the case.

 

[Tom Ogilvy]

Guess I don't need to clear the error if it didn't occur <g>

Private Sub Cmdbtnpop1_Click()
on Error Resume Next
res = WorksheetFunction. _
VLookup(txtloanMemNo1.Value,Worksheets("Sheet2"). _
Range("A1:B500"),2)
if err.number = 0 then
TxtloanMemName1.Value = res
Else
err.clear
TxtloanMemName1.Value "Not found"
End if
End Sub

--
regards,
Tom Ogilvy

Private Sub Cmdbtnpop1_Click()
on Error Resume Next
res = WorksheetFunction. _
VLookup(txtloanMemNo1.Value,Worksheets("Sheet2"). _
Range("A1:B500"),2)
if err.number = 0 then
err.Clear
TxtloanMemName1.Value = res
End if
End Sub


If the value being searched for is not found, then the vlookup raises a
trappable error. So you have to account for this. Also, not sure what
"database" means in your procedure, but a valid range reference must be
used. If you have a defined name "database", you could use
Range("Database") but it wasn't clear that is the case.