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 <gacl...@sprynet.com>
'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

0
dan
2/26/2010 12:05:04 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1003 Views

Similar Articles

[PageSpeed] 57

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 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 <gacl...@sprynet.com>
> '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
0
Dave
2/26/2010 3:06:05 AM
Thank you, Dave! I couldn't respond sooner since I was out of the
office Friday playing music for elementary school kids.

Dan
0
dan
3/1/2010 6:17:56 PM
Reply:

Similar Artilces:

Excel 2003 - VBA
Hi Guys: A quick question on the "Workbook Open" event: When I load up Excel and open the workbook, this event triggers. If I then close the workbook, without closing Excel, then open the workbook, it does not occur. Close down the worksheet and Excel, then open it, it triggers. Is this normal operation? Could it have to do with the possibility the I had set Application.EnableEvents = False, before exiting the workbook. Craig Also put in sub auto_open() -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Craig Brandt" <brandtcraig...

Lock and Unlock cells using VBA
Hello All Anyone know how to lock cells using VBA, also unlock others currently locked. Page protection will be in use at the time this needs to be done. Also, is there anywhere on the net with a complete list of VBA functions, including a short statement of what each one does, with or without examples. If not, any good books that cover the above area. I'm really (strange as it might seem), enjoying playing with VBA and seeing exactly what it can do. Regards Peter The easy questions... Excel's Help is a very good source. take a look at Peter Nonely's workbook that describes...

Help with Syntax for Requery
What is wrong with my syntax? It says its not found. Forms![frmGroup]!Form![sfrmCustGroup_C1].Requery -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200801/1 Hi Matt, the syntax for a subform on a form is like this: Forms![frmGroup]![Name of subform control]!Form.Requery if sfrmCustGroup_C1 is the name of the subform inside the subform control, use the name of the subform control instead. A form with a subform control is similar to having an image control with a picture inside it. The form h...

Please help with getting the right structure from dataset using GetXML()
Hi everyone, I need your help getting my getXML method to function the way I need it to. I have a stored procedure that returns data into a dataset (I am using VB.NET). I use getXML with dataset and I get the following result: <NewDataSet> <Table> <StudentFirstName>John</StudentFirstName> <StudentMiddleName>J</StudentMiddleName> <StudentLastName>Smith</StudentLastName> <StudentAddressType>Permanent</StudentAddressType> <StudentAddressLine1>123 Some Rd</StudentAddressLine1> <StudentAddressLine...

exchange advanced delegate mailbox rights
Hi, I would like to delegate the rights to change users mailbox rights to a group of technicians. In the case someone would like to give access to another person to his own mailbox. I can do it by giving "Full mailbox access" to the user from Exchange Advanced - Mailbox rights on the user object. (Delagation from outlook is not fine for our need). I don't know which attributes I need to select in the user objet security in order to delegate this rights. Thanks for your help Christophe Christophe, 'Mailbox Rights' are security items within the Exchange DB, not a...

Missing Sum at Bottom Right Corner
When I highlight a few cells, the sum of the numbers should show up i the bottom right corner of my screen. However, this is not appearing. How do I turn this feature on -- Ramro ----------------------------------------------------------------------- Ramrod's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2689 View this thread: http://www.excelforum.com/showthread.php?threadid=47083 Make sure the status bar is turned on: tools|options|View tab|in the Show Section. Make sure that the statusbar is visible. window|arrange|tiled (and resize by hand) rightclick ...

Writting Function using VBA
Hi I am trying to write a function to return an address but instead I get #VALUE!. Public Function fnd(a, b) fnd = Range(a).Find(b).Address End Function Please help. Thanks. ..Find won't work in UDF's called from the worksheet until xl2002. Depending on the range (a), you could use application.match() through each column. If the range is small, you could just loop through the values in that range, too. nc wrote: > > Hi > > I am trying to write a function to return an address but > instead I get #VALUE!. > > Public Function fnd(a, b) > >...

Populate List Box
Need some ideas. I have a list box from which our clients will be able to select which form they want to preview or print. However, each client should only have selections available that apply specifically to their company. For example: Company A will require forms 1, 2, 3, 6 and 7 Company B will require forms 1, 6 and 7 Company C will require forms 4, 5 and 6 I have some code right now to handle a simple two form variation: If Forms("Main").Controls("Form1Req") = "Yes" and Forms("Main").Controls ("Form2Req") = "No" Then Me.L...

hyperlink with vlookup
=HYPERLINK[=vlookup(a2;CARATULAS!$A$4:$D$146;4;0)] I´ve got to create an hyperlink that (according to a value in the same row) takes me to the same value in another worksheet. Please help me!- Thanks One play which could do this .. Link to a sample file at: http://www.savefile.com/files/6398176 File: AutoHyperlink_Mariano922_wksht.xls Assume you have this set-up In Sheet1, cols A & B, data from row2 down ------------ Prod# UnitPrice 1111 10 1112 20 1113 30 1114 40 1115 50 etc In CARATULAS -------- Prod# are listed in col A & BulkPrice in col D (col D is the lookup col), wi...

align left and align right buttons greyed out
command buttons for left align and right align are permanently greyed out. Default for new docs is left align so not much problem but there is no way to right align. I am using Word 2007. Any ideas? ...

Using possibly If and Vlookup
I have a spreadsheet where I want to first of all look up hours worked in cell N2 (within this cell is the number 35.00 or the number 37.00). I then want to use this cell to do a lookup bringing back one salary if N2 is 35 or another salary if N2 is 37. In my main spreadsheet I have a spinal column point in cell J2. I have created two lookup tables on another spreadsheet named salary35 and salary 37. These tables show the spinal point in one column and the appropriate salary in anoter column. eg salary35 1 11187 3 11534 5 11899 7 12246 9 12629 salary 37 1 11827 3 12193 5 12579 7 12...

Mailbox Rights #4
On Exchange 5.5, I can log in to any user's mailbox using an account with the "Service Account Admin." permission. On Exchange 2003, what permissions do I need on a mailbox in order to do the same thing? When I use that same Service Account Admin. account (my Exchange 2003 joined my Exchange 5.5 site) to log in to a user's mailbox, I get an error in Outlook that I am unable to open the folders and Outlook quits. Thank you. Hello, This KB article will help you out: http://support.microsoft.com/default.aspx?scid=kb;[LN];821897 Hope that helps, Russ Maxwell This post...

VBA to default printer in Crystal Report
is it possible to set default the printer for crystal report to be the same as the printer setup in GP (Files>Print Setup)? eg: is GP is using Printer1, then when calling a crystal report using VBA, the printer will be set to Printer1. If GP is using Printer2, then crystal report will be defaulted to Printer2. thks in advance. Unfortunately that isn't going to work for a couple reason: 1. The biggest reason is there isn't any way to determine what the printer is in Dynamics. Not even with Dexterity. It just wasn't something that was exposed. So I can't think of ...

Email not going to the right palce.....
not a good subject name, but.... We have exchange 2003 and use outlook 2003 We have a user, call him tom. Tom has an email account on the exchange tom@mycompany.com he has another internet account that is his primary account.. he wants the email sent to him from within the company to go to that account.... call it tom@othercompany.com From within the company, if someone sends an email to tom@othercompany.com, it goes to his exchange account WHY? The only email he has set up in his profile is tom@othercompany.com.......... it's as if all it is looking at is the TOM portion of the ad...

Right margin
I have gone through several processes...word pad, master page but can't not get anything to print within 1" on right margin in Publisher. In other programs (Word, Excel etc) this is not a problem, therefore it doesn't appear to be a printer issue. What am I missing? katemc wrote: > I have gone through several processes...word pad, master > page but can't not get anything to print within 1" on > right margin in Publisher. In other programs (Word, > Excel etc) this is not a problem, therefore it doesn't > appear to be a printer issue. What am I miss...

Conditional Formatting in VBA in 2007
I have a series of non-contiguous rows that I want to apply conditional formatting to. Right now, my code snip looks like this: With Range(sBegRange, sEndRange) .FormatConditions.Add Type:=xlExpression, _ Formula1:="=" & sCFCell & ">40" .FormatConditions(i).Font.Color = 3 .FormatConditions(i).StopIfTrue = False End With Where sCFCell is a string value for the cell I want evaluated for the formula, and i is an integer that increments for each time I create a new rule (which I'm doing for each row I format). It...

Vlookups and formats
I am using the following formula to pull in the worksheet name into cell B5 =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) The worksheet name is an account number. I then use vlookups to look at B5 as the lookup value to retreive various points of data linked to that specific account number. The problem I have is the formula shown adove brings the sheet name in as text and the vlookup sees it as text. I know this because when I edit the function the returned value for the lookup value is in quotes "1234". If I type i...

Is RMS right for me?
I just would like to know if RMS can handle 7 large supermarkets connected to each other via HQ. Can it handle large volume of transactions or its just really for "small reatail" stores? mymobile wrote: > I just would like to know if RMS can handle 7 large supermarkets connected to > each other via HQ. Can it handle large volume of transactions or its just > really for > "small reatail" stores? Hi mymobile, I work with RMS into five supermarkets (food markets) with fiscal printer (italian market). The problem of RMS are discounts.. the markets has mor...

VLOOKUP 1st Friday in April and So On
Formula: =VLOOKUP(A2,DatesList!$A$2:$F$386,6,FALSE) returns #N/A. The Lookup Value = 1st Friday in April. The Table Array is the DateList in another spreadsheet. The Col_index_num is 6 and the Range_Lookup is set to "False". Both the Lookup Value column and Column 6 are formatted as TEXT. I used the Text to Columns feature to ensure both columns REALLY were formatted at Text. I have tried formatting both columns as General. I have used TRIM to remove leading and trailing spaces. I have used LENB to make sure the number of characters matched, and they do. I hav...

Vlookup #7
Hello, I have a list where I am doing a look up from another sheet based on an employees ID number. The 2nd spread sheet is a listing of all the employee activites they did that day expressed through different codes. My question is how do I bring back all the information if using a vlookup only brings back the first match and some employees have multiple activity codes for that day. Is there a function out there to solve this problem? Thank you, Denise The easiest thing that comes to my mind is using PIVOT TABLES. In a column drag employee ID, in data you can have your code cou...

Printing: repeating columns on left and RIGHT
I know in File-Page Setup I can specify the columns to repeat at the left of each printed page. For my spreadsheet however, I need to repeat some columns from both the left and RIGHT sides of my spreadsheet on each printed page. Is there a way to do this? ...

non vba way to print non continuous ranges #2
Thanks Myrna, I didn't know about the hide columns facility. It makes what I want to do a lot easier : -- DavidObei ----------------------------------------------------------------------- DavidObeid's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=223 View this thread: http://www.excelforum.com/showthread.php?threadid=27196 ...

is there anyway in an excel macro to reorder the sheet tabs from left to right?
is there anyway in an excel macro to reorder the sheet tabs from left to right? Hi Daniel, see http://www.mvps.org/dmcritchie/excel/buildtoc.htm#sortallsheets --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Daniel" <softwareengineer98037@yahoo.com> wrote in message news:OctZdyBeFHA.3028@TK2MSFTNGP09.phx.gbl... > is there anyway in an excel macro to reorder the sheet tabs from left to > right? > > ...

Vlookup #4
I am using the vlookup function to look up data over several columns. If there is nothing in one column, I would like for it to return a blank cell instead of the 0 it returns now. Is there something else I can put in the formula to do this. Any help will be appreciated. Thanks in advance. Todd, Best thing to do would be to post the formula that you're using. You could test to see if the vlookup returns a zero and change it to a blank or you may just want to set your page up to not display zeros. Tools/Options/View & uncheck "Zero Values" John "Todd" <...

Vlookup - Match
Hi, I need to know an explanation to each of the following formulas other than that they gave the same result: =AVERAGE(INDEX(SHEET1!I:J,MATCH($A$3,SHEET1!$B:$B,0)0)) =VLOOKUP($A$3,SHEET1!$B$14:$DO$83,18,FALSE) COLOMN k Represented in number 18 is the average of I:J Hi Khalil, Is the setup of your question correct? Perhaps it is, but - 18, counted from B gives column S, I think. - INDEX, I think, returns one single cell. So what's the need of AVERAGE around? - Your first function doesn't function ... at least: it give an error with me. Shouldn't it be: =INDEX(Sheet1!I:J...