Application.vlookup Error 2023
Forums Excel Questions V look up problem in vba Results 1 to 3 of 3 V look up problem in vbaThis is a discussion on V look up problem in vba within the Excel Questions forums, part of the Question Forums vba vlookup error 2042 category; Hi All I'm having a problem with the little macro below,I keep getting the runtime excel vba vlookup error "Could not set the ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Oct 1st, 2014,04:49 PM #1 paulsolar Board Regular Join Date Aug 2013 Location South Wales Posts 504 V look up problem in vba Hi All I'm having a problem with the little macro below,I keep getting the runtime error "Could not set the value property. Type mismatch" On inspection the ListBox1.Value is returning the correct value (from hovering over the correct part of the formula), but MyVar is returning the error 2023 (from hovering over the correct part of the formula) which equates to (#REF!) This leads me to think that there is something wrong with the vlookup but I cant see where. I've set Listbox1 to single selection so I think that using .value is sufficient to follow Listbox1 Any ideas as to where I've gone wrong would be greatly appreciated Thanks in anticipation Kind regards Paul Code: Private Sub CommandButton1_Click() Dim MyVar As Variant MyVar = Application.VLookup(ListBox1.Value, Sheet3.Range("B3:B500"), 3, False) 'MsgBox MyVar TextBox1.Value = MyVar End Sub Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Oct 1st, 2014,04:51 PM #2 NeonRedSharpie Board Regular Join Date Jul 2014 Posts 1,661 Re: V look up problem in vba You're looking for column 3 of a 1 column range. Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Oct 1st, 2014,04:57 PM #3 paulsolar Board Regular Join Date Aug 2013 Location South Wales Posts 504 Re: V look up problem in vba thanks very much, it has been driving me mad. It's always the blindingly obvious that gets overlooked. If I had been hear all night I wouldn't have noticed it Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote « Previous Thread | Next Thread » Like this thread? Share it with others Like this thread? Share it with others Twitter Linked In Google Reddit StumbleUpon Posting Permissions You may not post new threads You may not post replies You may not post attachments You may not edit y
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Why am I getting http://www.mrexcel.com/forum/excel-questions/809112-v-look-up-problem-visual-basic-applications.html Error 2042 in VBA Match? up vote 3 down vote favorite 1 I have Column A: +--+--------+ | | A | +--+--------+ | 1|123456 | |--+--------+ | 2|Order_No| |--+--------+ | 3| 7 | +--+--------+ Now if I enter: =Match(7,A1:A5,0) into a cell on the sheet I get 3 As a result. (This is desired) But when I enter this line: Dim CurrentShipment As Integer CurrentShipment http://stackoverflow.com/questions/15526784/why-am-i-getting-error-2042-in-vba-match = 7 CurrentRow = Application.Match(CurrentShipment, Range("A1:A5"), 0) CurrentRow gets a value of "Error 2042" My first instinct was to make sure that the value 7 was in fact in the range, and it was. My next was maybe the Match function required a string so I tried Dim CurrentShipment As Integer CurrentShipment = 7 CurrentRow = Application.Match(Cstr(CurrentShipment), Range("A1:A5"), 0) to no avail. excel excel-vba excel-2007 share|improve this question asked Mar 20 '13 at 14:45 user2140261 5,17661938 Your code works for me as is. The error message you are getting is the equivalent of #N/A. Perhaps it is focusing on the wrong sheet sometimes? You might try actively selecting the right sheet before running the code. –techturtle Mar 20 '13 at 15:08 add a comment| 5 Answers 5 active oldest votes up vote 2 down vote accepted Try this : CurrentRow = Application.Match(CLng(CurrentShipment), Range("A1:A5"), 0) share|improve this answer answered Mar 20 '13 at 14:50 Vincent MAURY 894 This worked, but why? –user2140261 Mar 20 '13 at 14:52 And it will work for whole numbers, but not for decimal and not for string, so it feels a bit dodgy... &n
is coming from the excel help VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) How can i use this function in macro where the lookup_value is in A1 format, the table_array is a variable (rng) ? the lookup funtion is rffered to http://www.pcreview.co.uk/threads/vlookup-syntax-format.3395065/ another sheet in the same workbook e.g. sheet1 is where the function works but the https://www.experts-exchange.com/questions/27616012/VBA-Worksheet-Function-v-Application-error-handling-or-not.html reference array is in sheet2. Yossi evenzur, Jan 23, 2008 #1 Advertisements Mike H Guest Yossi, Maybe:- Sub stance() Set rng = Sheets("Sheet2").Range("A1:B10") lookupvalue = Sheets("Sheet1").Range("A1").Value myvalue = Excel.WorksheetFunction.VLookup(lookupvalue, rng, 2, False) MsgBox myvalue End Sub Mike "Yossi evenzur" wrote: > Hi > the floowing line is coming from the excel help > VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) > How can vba vlookup i use this function in macro where the lookup_value is in A1 format, > the table_array is a variable (rng) ? > the lookup funtion is rffered to another sheet in the same workbook e.g. > sheet1 is where the function works but the reference array is in sheet2. Mike H, Jan 23, 2008 #2 Advertisements Yossi evenzur Guest Hi i haven't tested it yet but rng is changing for eacj workbook so application.vlookup error 2023 i need the macro to find it itself e.g. run "currentreggion" and get the range but i can't make the syntax, can you help? "Mike H" wrote: > Yossi, > > Maybe:- > > Sub stance() > Set rng = Sheets("Sheet2").Range("A1:B10") > lookupvalue = Sheets("Sheet1").Range("A1").Value > myvalue = Excel.WorksheetFunction.VLookup(lookupvalue, rng, 2, False) > MsgBox myvalue > End Sub > > Mike > > "Yossi evenzur" wrote: > > > Hi > > the floowing line is coming from the excel help > > VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) > > How can i use this function in macro where the lookup_value is in A1 format, > > the table_array is a variable (rng) ? > > the lookup funtion is rffered to another sheet in the same workbook e.g. > > sheet1 is where the function works but the reference array is in sheet2. Yossi evenzur, Jan 23, 2008 #3 Yossi evenzur Guest Hi Now i tested it, i modifyed the syntax a bit but i get run-time error 1004 "unable to get Vllookup property of the WorksheetFunction class here is the syntax Sub Add_TRF_CounterId() Rows("1:1").Select Selection.Insert Shift:=xlDown ' insert empyt row Range("A1").Select 'select cell a1 to run vlookup Set rng = Sheets("57, P_NBSC_SERVICE").Range("A2:c300") lookupvalue = Sheets("Sheet 1").Range("A2").Value 'the next line gives the error massage myvalue = Excel.WorksheetFunction.VLookup(lookupvalue, rng, 3, Fal
Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > VBA Worksheet Function v. Application : error handling - or not? Want to Advertise Here? Solved VBA Worksheet Function v. Application : error handling - or not? Posted on 2012-03-04 MS Excel VB Script Programming 1 Verified Solution 4 Comments 1,646 Views Last Modified: 2012-03-06 follow up from difference between WorksheetFunction and Application ? Q_27607747 Assumption: program coding errors excluded. Most of this is derived by me from EEID: 37675139 Kevin ------------------------------------------------------------------------------------------------------------ Application object returns 7 worksheet errors via the Variant error sub type which is not Boolean. It's an error type which corresponds to the Excel formula/function errors and which can be one of: Error 2000 (#NULL!) Error 2007 (#DIV/0!) Error 2015 (#VALUE!) Error 2023 (#REF!) Error 2029 (#NAME?) Error 2036 (#NUM!) Error 2042 (#N/A) (each one of these 7 numbers are returned mutually exclusively in the one variant error sub type) The error numbers are returned within vba for the programmer and the spreadsheet errors within the spreadsheet for the user. ------------------------------------------------------------------------------------------------------------ Worksheet object just crashes (halts execution) with a run time error "When using the WorksheetFunction object and trapping VB/VBA generated errors, the error code is usually a 1004 Unable to get the ... property of the WorksheetFunction class - not a very useful error code." which amounts to one code 1004, which as I recall (can't find it now) is in fact a catch all code given that means vba is reporting an error from "some other" (foreign) application. I think. In this case the error result is presented as a program failure if not handled. ------------------------------------------------------------------------------------------------------------ so Application Object is native vba meaning vba knows what to do, so author error handling not required because run time halt will not occur, error results showing in sheet. Worksheet Functio