Excel Vba Error 1004 Unable To Get The Vlookup Property
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings excel vba error 1004 unable to get the vlookup property of the worksheetfunction class and policies of this site About Us Learn more about Stack Overflow vba runtime error 1004 unable to get the vlookup property of the worksheetfunction class the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation excel vba error 1004 unable to get the match property of the worksheetfunction class 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; run time error 1004 unable to get the vlookup property it only takes a minute: Sign up “Unable to get the VLookup property of the WorksheetFunction Class” error up vote 6 down vote favorite I am trying to develop a form to track invoices as they come in. The form will have a combobox where I can click on and select a vendor number. I want the textbox to automatically
Excel Vba Error 1004 Application-defined Or Object-defined Error
fill in based on the vendor number selected from the combobox. Here's what I have so far: Private Sub ComboBox1_Change() 'Vlookup when ComboBox1 is filled Me.TextBox1.Value = Application.WorksheetFunction.VLookup( _ Me.ComboBox1.Value, Worksheets("Sheet3").Range("Names"), 2, False) End Sub Worksheet 3 is from which the information is being drawn (the vendor number and name). When I go back to the form to test the code, I get the following error: Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class How do I fix this? excel vba excel-vba vlookup share|improve this question edited Aug 15 '14 at 7:13 Jean-Francois Corbett 23.9k1678113 asked Oct 9 '13 at 19:05 user2864307 31112 2 If the VLOOKUP() fails (can't find a match), you will get this error. –Gary's Student Oct 9 '13 at 19:10 add a comment| 3 Answers 3 active oldest votes up vote 6 down vote Try below code I will recommend to use error handler while using vlookup because error might occur when the lookup_value is not found. Private Sub ComboBox1_Change() On Error Resume Next Ret = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Worksheets(
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
Excel Vba Error 1004 Select Method Of Range Class Failed
developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask excel vba error 1004 document not saved 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 excel vba error 1004 method range of object _global failed them; it only takes a minute: Sign up Why is VLookup in VBA failing with runtime error 1004? up vote 2 down vote favorite Spreadsheet "Sheet3" looks like this: S&P 500 DJIA 1/1/1991 795.4476 2973.09 1/2/1991 786.3856 2947.1 1/3/1991 http://stackoverflow.com/questions/19280477/unable-to-get-the-vlookup-property-of-the-worksheetfunction-class-error 775.4636 2905.19 1/4/1991 773.5364 2896.8 1/7/1991 760.2996 2847.9 1/8/1991 759.0029 2832.81 1/9/1991 750.8416 2788.67 1/10/1991 758.1719 2820.8 Also Cell "F2" is literally a copy and paste of 1/7/1991 cell. VBA Code looks like this: Sub badlook3() Dim BenchSI As Variant Dim BRange As Range Dim SIDate As Date Set BRange = Worksheets("Sheet3").Range("A2:C9") MsgBox BRange.Address SIDate = Worksheets("Sheet3").Range("F2").Value BenchSI = Application.WorksheetFunction.VLookup(SIDate, BRange, 2, True) End Sub I am getting the "Unable to get the VLOOKUP property of the WorkSheet Function http://stackoverflow.com/questions/11055687/why-is-vlookup-in-vba-failing-with-runtime-error-1004 class" error. What am I missing here? Column A is in the right order. They are dates. What does Excel want from me? vba excel-2007 vlookup share|improve this question edited Jun 15 '12 at 19:46 the Tin Man 109k22135206 asked Jun 15 '12 at 17:43 Tommy Z 1325716 add a comment| 2 Answers 2 active oldest votes up vote 2 down vote accepted You are asking vLookup to return on a 2 column range, against a 1 column range. Change BRange = "A2:B9" to make your vLookup pick up the S&P Value. Alternatively, you can change the range to A2:C9 and change the 2 to a 3 in your vLookup and get the DJ average. In short, vLookup can only return a column reference to the greatest amount of columns in a range. It can return the 1st, 2nd, 0r 3rd column reference in a 3 column range, but not the 4th, because there is no 4th column. share|improve this answer answered Jun 15 '12 at 17:50 Scott Holtzman 15.8k31539 Thanks! That was a typo. I tried to make it a lot smaller to ask the question. I made the change to the Range but still have the same error. Any thoughts? –Tommy Z Jun 15 '12 at 18:16 And I really appreciate your help Scott! - tommy –Tommy Z Jun 15 '12 at 18:16 If his
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 http://stackoverflow.com/questions/15574632/excel-vba-unable-to-get-the-vlookup-property 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; http://www.vbaexpress.com/forum/showthread.php?37177-Solved-VLookup-Code-Error-in-form-Runtime-Error-1004 it only takes a minute: Sign up Excel vba unable to get the Vlookup property up vote 1 down vote favorite I know there are a lot of these questions on here but I can't see what is going error 1004 wrong. I have the following code which checks whether a value is in a column in another workbook. Dim masterWbk As Workbook Dim oWbk As Workbook Dim RowCount As Integer Dim LookupRange As Range Dim Exists As Variant Dim a As Integer Dim i As Integer Dim jobnumber As String RowCount = WorksheetFunction.CountA(Sheets("Sheet1").Range("A1").EntireColumn) masterWbk.Activate Set LookupRange = masterWbk.Sheets("Sheet1").Range("C1:C100") a = 0 For i = 0 To RowCount - 1 jobnumber = oWbk.Sheets("Sheet1").Range("A2").Offset(i, 0).Value ' On Error GoTo ExistsError: Exists = excel vba error Application.WorksheetFunction.VLookup(jobnumber, LookupRange, 1, False) Now the value is definitely in the the lookup range and is formatted the same but the Vlookup just won't work. It works fine as an excel formula. What have I missed? excel excel-vba share|improve this question asked Mar 22 '13 at 15:54 evoandy 28811028 You can use .Find instead of Vlookup –scott Mar 22 '13 at 16:04 Where have you set the masterWbk and oWbk? –Siddharth Rout Mar 22 '13 at 16:04 I've set them further up in the code but left them out because it's not that which is causing the issue. –evoandy Mar 22 '13 at 16:08 are you getting an error, or is Exists = false? Is it case sensitive? Are you searching for a numeric value or a string value? –Our Man In Bananas Mar 22 '13 at 16:09 it's giving me run-time error 1004. It is searching for a string. It is formatted the same in both workbooks so it shouldn't matter –evoandy Mar 22 '13 at 16:18 | show 1 more comment 3 Answers 3 active oldest votes up vote 3 down vote accepted Using find it'd look like this- Dim masterWbk As Workbook Dim oWbk As Workbook Dim RowCount As Integer Dim LookupRange As Range Dim Exists As Variant Dim a As Integer Dim i As Integer Dim jobnumber As String RowCount = Wo
VBA Code & Other Help Excel Help Solved: VLookup Code Error in form - Runtime Error 1004 If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 4 of 4 Thread: Solved: VLookup Code Error in form - Runtime Error 1004 Thread Tools Show Printable Version Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 04-25-2011,08:17 AM #1 bdsii View Profile View Forum Posts View Blog Entries View Articles VBAX Contributor Joined Jul 2009 Posts 157 Location Solved: VLookup Code Error in form - Runtime Error 1004 I am trying to create a simple spreadsheet (see attached) with a form that the user would enter the Contributor Number and a Vlookup is performed on a range and when exiting the field would return the Contributor Name and Contributor Address into fields on the form. The code sets the range that is used in the Vlookup. When running this, I get an error: Runtime Error 1004: Unable to get the Vlookup property of the Worksheet Function class. I have looked all over the web but cannot find a solution that works in this situation, so I am not sure where to go from here. The code I am using is: Private Sub ContributorNumber_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim myrange Set myrange = Worksheets("Contributors").Range("A1:B143") ContributorName.Value = WorksheetFunction.VLookup(ContributorNumber.Value, myrange, 2, False) If Err <> 0 Then ContributorName.Value = "N/A" End If ContributorAddress.Value = WorksheetFunction.VLookup(ContributorNumber.Value, myrange, 3, False) If Err <> 0 Then ContributorAddress.Value = "N/A" End If End Sub Formatting tags added by mark007 Does anyone have an idea why this code is not working ? When I tried to debug the code, when I hovered over ContributorNumber.Value, it showed the proper value entered into the form and I used values for ContributorNumber that I knew were present in the range I set-up. Any advice would be greatly appreciated ! I think it is probably something small I am overlooking but cannot find it. thanks ! Attached Files To view attachments your post count must be