Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the right syntax for vlookup in vba?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the right syntax for vlookup in vba?
Thank you, Dave! I couldn't respond sooner since I was out of the
office Friday playing music for elementary school kids. Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup syntax format | Excel Programming | |||
VLookUp Syntax Error ? | Excel Programming | |||
application.vlookup syntax | Excel Programming | |||
Vlookup syntax | Excel Programming | |||
Vlookup Syntax Error | New Users to Excel |