Application-defined Or Object-defined Error Vlookup
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 Learn more about Stack Overflow the company Business Learn application defined or object defined error excel more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users application defined or object defined error access Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping
Application Defined Or Object Defined Error 32809
each other. Join them; it only takes a minute: Sign up Getting Application-defined or object-defined error up vote 2 down vote favorite I'm new to both Excel/VBA and StackOverflow. I'm running Excel version 2007 I have spent all
Application Defined Or Object Defined Error In Macro
day on this problem and I have exhausted my patience. I keep getting "Application-defined or object-defined error" when attempting to perform a simple VLOOKUP. I'm testing this sample code to get to my bigger issue: I need a Command Button located in Sheet1 that will have VBA code to perform a table lookup based on a cell value in Sheet1. The table is defined in Sheet2. The lookup (VLOOKUP I assume) will need to lookup the application defined or object defined error in vba name and send back data that contains say "Salary" for example. This salary information is then inserted in another cell on Sheet1. The user can then update this Salary figure. Another Command Button will export it back to the table (Update the table entry). Is this possible? However I cannot get passed simple code to display a message box via a VLOOKUP (see below). Here is my Sheet1: A1:4 Dave John Sara Steve Here is my Sheet2 which is defined as Table1 (A2:B6) Name Salary Dave 2500 John 3500 Sara 4000 Steve 4500 Here is my VBA code: (note the comments on severy "tries") Sub FINDSAL() Dim E_name As String Dim Res As Variant 'On Error Resume Next 'Err.Clear 'ThisWorkbook.Sheets("Sheet2").Activate 'ActiveSheet.Range("A1:B5").Select E_name = "John" 'Res = Application.WorksheetFunction.VLookup(E_name, Sheet1.Range("A2:B5"), 3, False) Res = Application.WorksheetFunction.VLookup(E_name, Table1, 2, False) MsgBox "Salary is: $" & Res End Sub excel vba vlookup share|improve this question edited Jan 30 '14 at 22:08 afuzzyllama 4,76342854 asked Jan 30 '14 at 22:03 user3255090 134 Add Option Explicit to the top of your code. You will find your error immediately. –enderland Jan 30 '14 at 22:34 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote accepted Try this code. FINDSAL gets name from sheet1 A1 (you can easily change it), finds the salary in sheet2 range Table1 and if s
here for a quick overview of the site Help Center Detailed answers to any questions you might
Application Defined Or Object Defined Error Vba 1004
have Meta Discuss the workings and policies of this site About application defined or object defined error vba formula Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads application defined or object defined error vba range 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 http://stackoverflow.com/questions/21468369/getting-application-defined-or-object-defined-error programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Application defined or object defined error 1004 and Error 438 in using vlookup formula up vote 0 down vote favorite I am trying to use the vlookup function on a range of cells, that looks up values in other http://stackoverflow.com/questions/34383785/application-defined-or-object-defined-error-1004-and-error-438-in-using-vlookup sheets. However I ckeep getting a runtime error that says "application defined or object defined error." ActiveSheet.Range("$A$1", Selection.End(xlDown)).RemoveDuplicates Columns:=1, _ Header:=xlYes 'In Summary Tab Range("A1").CurrentRegion.Select nRows = Selection.Rows.Count ' Places column headers in "Summary" tab For iCounter = 2 To Sheets.Count Sheets(iCounter).Select Range("A1").CurrentRegion.Select nCols = Selection.Columns.Count For iColumn = 2 To nCols Sheets(iCounter).Select If (WorksheetFunction.IsNumber(Cells(2, iColumn)) = "TRUE") Then Cells(1, iColumn).Select Selection.Copy Sheets("Summary").Select ActiveCell.Offset(0, 1).PasteSpecial Application.CutCopyMode = False ActiveCell.Offset(1, 0).Select ActiveCell.Resize(nRows - 1, 1).Select Selection.Formula = "=vlookup(B2," & _ Range(sheets(icounter).selection).Address","& icolumn",false)" End If Next Next I also tried editing the vlookup formula to this, (with everything else the same): Selection = Application.WorksheetFunction.VLookup( _ "B2", Sheets(iCounter).CurrentRegion.Select, iColumn, False) But this gives error 438 "object doesn't support this property or method" Even tried editing the vlookup to this but got the 1004 error again: Selection = Application.WorksheetFunction.VLookup("B2",Sheets(iCounter).Range(Cells(1, 1), Cells(nCols, nRows)), iColumn, False) excel-vba runtime-error vlookup application-error share|improve this question edited Dec 20 '15 at 18:03 asked Dec 20 '15 at 17:31 mike_326 154 add a co
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 http://www.vbforums.com/showthread.php?577213-RESOLVED-vlookup-error-1004 below. Results 1 to 3 of 3 Thread: [RESOLVED] vlookup error 1004 Tweet Thread Tools Show Printable Version Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Jul 21st, 2009,06:26 AM #1 ratroutus View Profile View Forum Posts Thread Starter Junior Member Join Date Apr 2006 Posts 17 [RESOLVED] vlookup error 1004 dear all, this is my small little tiny macro that i'm trying to run in excel For i = 3 To 2113 Range("AG" & i).Select defined or ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-30],cost!R[6 - " & i & " ]C[-31]:R[2281 -" & i & "]C[-30],2,FALSE)" Next i i have two sheets i'm trying to look for a value in the second sheet and return matching value... i'm getting the error run-time error 1004: object defined or application defined error. thank you for your help Reply With Quote Jul 24th, 2009,08:53 AM #2 Resource Dragon View Profile View Forum Posts Addicted Member Join Date Jun 2009 Location Townsville, Qld, Australia Posts 135 Re: defined or object vlookup error 1004 I tried using VLOOKUP in visual basic and ran into runtime error 1004 also. A colleague suggested that I try using an array instead. My example relates to a user form with a combo box called cmb_thing_id and a label called lbl_thing_acty. (There are some other lines I've left out here to allow for the cases where the value the user puts in the combo box does not correspond to a value in the list.) This is the code I use: Sub thing_acty Dim THING(300, 300) As String For i = 2 To 300 THING(1, i) = Sheets("Sheet2").Cells(i, 1) Next i For j = 2 To 300 THING(2, j) = Sheets("Sheet2").Cells(j, 2) Next j For k = 2 To 300 If THING(1, k) = cmb_thing_id.Value Then lbl_thing_acty.Caption = THING(2, k) Exit For End If Next k End Sub In a later version of this I just used a 1 dimensional array and then directed the program over to the next column once it had found a matching value, which saves the amount of memory the computer is using to run the sub. Reply With Quote Jul 24th, 2009,09:02 AM #3 ratroutus View Profile View Forum Posts Thread Starter Junior Member Join Date Apr 2006 Posts 17 Re: vlookup error 1004 dear all, i have solved this... it was all about the syntax... this is my sub: sub vlookupmacro() dim myrange set myrange = Worksheets("sheet1").Range("B3:C1966) for i = 2 to 1414 range("AH" & i)