Error 1004 Unable To Get Vlookup Property Of Worksheetfunction Class
Contents |
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 run time error 1004 unable to get the vlookup property of the worksheetfunction class Learn more about Stack Overflow the company Business Learn more about hiring developers unable to get the vlookup property of the worksheetfunction class error handling or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack
Unable To Get The Vlookup Property Of The Worksheetfunction Class Vba
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 Excel VBA: Error 1004 WorkSheetFunction 'Unable to
Excel Vba Unable To Get The Vlookup Property Of The Worksheetfunction Class
get Vlookup property" up vote 4 down vote favorite 1 Trying to write a quick piece of VBA in Excel 2010 to Use Vlookup to find a value Return the value in the 3rd Column Set a given cell to this value My difficulty is with the formula. Sub Metrics123() Dim x As Integer x = Application.WorksheetFunction.VLookup("Test", "A7:D9", 3, False) Range("A1").Value = x End Sub When I unable to get the vlookup property of the worksheet function class run this I hit the error 1004: 'Unable to get the Vlookup Property of the WorksheetFunction Any pointers appreciated! excel vba excel-vba share|improve this question edited Apr 2 '14 at 11:30 asked Apr 2 '14 at 11:08 EmilioSandoz 28115 add a comment| 1 Answer 1 active oldest votes up vote 4 down vote accepted Two ways for you. 1) Use .Formula property: With ThisWorkbook.Worksheets("Sheet1").Range("A1") .Formula = "=VLOOKUP(""Justin"",A7:D9,3,FALSE)" .Value = .Value End With where .Value = .Value rewrites formula with it's result 2) use Application.VLookup with Range("A7:D9") instead "A7:D9": Dim x With ThisWorkbook.Worksheets("Sheet1") x = Application.VLookup("Justin", .Range("A7:D9"), 3, False) Range("A1").Value = x End With Note, that x should be Variant, because if nothing found, Application.VLookup returns Error 2042(#N/A) share|improve this answer edited Apr 2 '14 at 11:26 answered Apr 2 '14 at 11:12 simoco 27.5k93552 1 Excellent, thanks for the quick response. First option works fine. –EmilioSandoz Apr 2 '14 at 11:34 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your a
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
Vba Vlookup Error Handling
about Stack Overflow the company Business Learn more about hiring developers or posting ads error 2042 vba vlookup with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow vba unable to get the match property of the worksheetfunction class is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Error 1004 - Vlookup in vba - Unable to get the Vlookup http://stackoverflow.com/questions/22809515/excel-vba-error-1004-worksheetfunction-unable-to-get-vlookup-property property of the WorksheetFunction class up vote 1 down vote favorite I've browsed the various questions already asked with this issue other users have faced and none of the solutions seem to fix the error code coming up. I have a form which prompts the user for a reference number - they input this into the text field and then press OK 'OK button from form1 Public Sub CommandButton1_Click() refInput http://stackoverflow.com/questions/32272325/error-1004-vlookup-in-vba-unable-to-get-the-vlookup-property-of-the-workshee = refTextBox.Value InputRef.Hide ExportShipForm.Show End Sub Once this has been pressed, the next form appears which I would like to be populated with data based on the reference number input on the first form. I have an update button which will update the "labels" on the form to show the data - this is where I am getting an error. The first label to update is through a Vlookup: Below the users clicks the update button the 2nd form: Public Sub btnUpdate_Click() Call ICS_Caption lbl_ICS.Caption = Label_ICS End Sub This calls a function below: Public Sub ICS_Caption() Dim ws1 As Worksheet refInput = InputRef.refTextBox.Value Set ws1 = Worksheets("MACRO") dataRef = Worksheets("Shipping Data").Range("A:K") Label_ICS = WorksheetFunction.VLookup(refInput, dataRef, 7, False) End Sub The error continues to come up each time - I have ran the vlookup manually in a cell outside of VBA and it works fine. I have typed the range in the Vlookup whilst also using named ranges but each variation shows the same error. Eventually, I would want the label on form 2 to update with the result of the Vlookup. Any ideas? excel forms vba excel-vba vlookup share|improve this question edited Aug 28 '15 at 13:42 asked Aug 28 '15 at 13:33 jamesc100 35
Forums Excel Questions VLookup in VBA Getting Runtime error 1004: Results 1 to 8 of 8 VLookup in VBA Getting Runtime error 1004:This is a discussion on VLookup in VBA Getting Runtime http://www.mrexcel.com/forum/excel-questions/87434-vlookup-visual-basic-applications-getting-runtime-error-1004-a.html error 1004: within the Excel Questions forums, part of the Question Forums category; I'm getting Runtime error '1004': Unable to get the VLookup property of the WorksheetFunction class when I execute the following ... http://forum.chandoo.org/threads/run-time-error-1004-unable-to-get-the-vlookup-property-of-the-worksheet.9859/ 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 unable to Threaded Mode Jun 1st, 2004,04:22 PM #1 MPastor Board Regular Join Date Apr 2004 Location Orlando, FL Posts 136 VLookup in VBA Getting Runtime error 1004: I'm getting Runtime error '1004': Unable to get the VLookup property of the WorksheetFunction class when I execute the following line of code from within my VBA macro. Code: ActiveCell.Text = Application.WorksheetFunction.VLookup("A2", "Timelookuptable", 3) Have any suggestions? USING: Version Office 2000, Windows unable to get 2000 Professional. Share Share this post on Digg Del.icio.us Technorati Twitter Thanks.... ...Mike Reply With Quote Jun 1st, 2004,04:35 PM #2 Greg Truby MrExcel MVPInt'l Moderator Join Date Jun 2002 Location 39° 17' 15" N, -94° 40' 26" W Posts 9,851 Re: VLookup in VBA Getting Runtime error 1004: Try this... ActiveCell = Application.VLookup([a2], [Timelookuptable], 3, False) Share Share this post on Digg Del.icio.us Technorati Twitter Greg ……………………………………………… Home: XL 2003, 2007, 2010, and 2013 on Windows 7 Work: XL 2013, 2016 on Windows 10 Please use CODE tags - especially for longer excerpts of code. Reply With Quote Jun 1st, 2004,04:50 PM #3 MPastor Board Regular Join Date Apr 2004 Location Orlando, FL Posts 136 Re: VLookup in VBA Getting Runtime error 1004: Greg, your version works perfectly!!! Now, I'd like to "understand" why? I see three differences: 1. You used "activecell = " instead of my "activecell.text = ". I anticipated the result from the Vlookup being a text string and used the " .text"; 2. You enclosed the cell whose value I want to lookup in square brackets [a2] , rather than the typical "a2" (enclosed in quotations; and 3. You enclosed the range name for the lookup table
you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings. Yours, Chandoo Hi All Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam Post Spam and you Will Be Deleted as a User Hui... When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post. Welcome Message to New users Spam Sample File Run Time Error 1004. Unable to get the vlookup property of the worksheet Discussion in 'Ask an Excel Question' started by ThrottleWorks, Apr 22, 2013. ThrottleWorks Excel Ninja Messages: 1,500 Sir I am getting following error in the code. Run Time Error 1004. Unable to get the vlookup property of the worksheet function class The code was running fine for the last one month. The code is populating a vlookup, the base values are present in both the files. I am not able to understand the reason behind bug. Can anyone please help me in this. I have also provided link for the original query which was resolved. http://chandoo.org/forums/topic/vlookup-used-in-a-loop-not-working-after-first-2-instanes ThrottleWorks, Apr 22, 2013 #1 Hui Excel Ninja Staff Member Messages: 9,817 Sachinbizboy You have been a member here for nearly 18 months You should know that you have provided us no, information with which to help you solve the problem You haven't told us what has changed, What formula your using, what values cause the problem. Can I please ask that you read the Rules: http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting Hui, Apr 22, 2013 #2 ThrottleWorks Excel Ninja Messages: 1,500 Hui Sir apologies for my error, I agree this is a mistake. I am getting bug while running following loop. I have 2 different files, I am populating vlookup from book A to Book B. The loop rans for 5 or 6 rows then it thros bug, it is not giving bug at the start. The base value is present in both the