What is the right syntax for vlookup in vba?
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("tb lPriceListCore"), _
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 wrote:
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
'Date: Sun, 21 Jan 2001 19:36:37 -0500
'Subject: 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
|