What is the right syntax for vlookup in vba?

[dan dungan]

Hi,

Using Excel 2000, the code is returning an error--"runtime error. Type
mismatch" on this line:

Me.txt1.Text = Application.Evaluate("=VLookup(" & sCoreAdapShell & ",
tblPriceListCorePart, 5,False)")

I'm looking for feedback about the syntax for using vlookup in vba.

The above line is in the commandbutton--cmdCalc with the following
code:

Private Sub cmdCalc_Click()
Dim sCoreAdapShell As Variant
sCoreAdapShell = txtCore.Text & txtAdap_Config.Text & txtShell.Text
Me.txt1.Text = Application.Evaluate("=VLookup(" & sCoreAdapShell & ",
tblPriceListCore, 5,False)")
End Sub

For my lookup value, I'm using the variable--sCoreAdapShell. that
concatinates the data in three textboxes. txtCore and txtAdap_Config
are populated when I select a choice from a combobox. txtShell is
populated by the user.

For the table array, I'm using the named range--tblPriceListCore.

Thanks,

Dan

More details:

I have the following objects and code:
a userform--userform4

a combobox--cboFormula with the following code.

Private Sub cboFormula_Change()
'George Clark
'Newsgroups: microsoft.public.Excel.programming
'From: George Clark <[email protected]>
'Date: Sun, 21 Jan 2001 19:36:37 -0500
'Subject: Re: How can I populate a TextBox control in xl 2000

'Put the core part, multiplier and adapter configuration in textboxes
'for use in the vlookup to get the price
With cboFormula
txtCore = .Column(1, .ListIndex) ' Core Part
txtCore_Multiplier = .Column(2, .ListIndex) ' Multiplier
txtAdap_Config = .Column(3, .ListIndex) ' Adapter
configuration
End With
End Sub

 

[Dave Peterson]

Private Sub cmdCalc_Click()
Dim sCoreAdapShell As String 'it's all text, right?
dim res as variant 'could be an error

sCoreAdapShell = txtCore.Text & txtAdap_Config.Text & txtShell.Text

res = application.vlookup(scoreadapshell, _
thisworkbook.worksheets("sheetnamehere").range("tblPriceListCore"), _
5,False)

if iserror(res) then
'like #n/a in excel
Me.txt1.Text = "not found!"
else
me.text1.text = res
end if

End Sub

I guessed that tblpricelistcore was a named range in excel. If it was a range
variable that you set somewhere else, the line of code changes:

dim tblPriceListCore as range
....
set tblPriceListCore = thisworkbook.worksheets("sheetnamehere") _
.range("somerangehere")
....
res = application.vlookup(scoreadapshell, tblPriceListCore, 5, False)

 

[dan dungan]

Thank you, Dave! I couldn't respond sooner since I was out of the
office Friday playing music for elementary school kids.

Dan