Application.vlookup Error Handling
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss excel vba vlookup error 1004 the workings and policies of this site About Us Learn more
Excel Vba Vlookup Error 2042
about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow vba iferror vlookup 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
Excel Vba Vlookup #n/a
other. Join them; it only takes a minute: Sign up How to error handle 1004 Error with WorksheetFunction.VLookup? up vote 8 down vote favorite 2 I have this code: Dim wsFunc As WorksheetFunction: Set wsFunc = Application.WorksheetFunction Dim ws As Worksheet: Set ws = Sheets("2012") Dim rngLook As Range: Set rngLook = ws.Range("A:M") 'within a loop currName = vba vlookup not working "Example" cellNum = wsFunc.VLookup(currName, rngLook, 13, False) VLookup is not expected to always find a result; but when it does not find a result the line errors out before I can even error check it the next line. The error: Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class It works fine when a result is found. What's a good way to handle errors here? excel vba excel-vba share|improve this question edited Jul 7 '14 at 20:14 David Zemens 30.8k72755 asked Aug 5 '13 at 16:33 JBurace 99282851 add a comment| 3 Answers 3 active oldest votes up vote 4 down vote accepted There is a way to skip the errors inside the code and go on with the loop anyway, hope it helps: Sub new1() Dim wsFunc As WorksheetFunction: Set wsFunc = Application.WorksheetFunction Dim ws As Worksheet: Set ws = Sheets(1) Dim rngLook As Range: Set rngLook = ws.Range("A:M") currName = "Example" On Error Resume Next ''if error, the code will go on anyway cellNum = wsFunc.VLookup(currN
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
Vba Vlookup Not Finding Value
Learn more about Stack Overflow the company Business Learn more about hiring developers
Application.vlookup Vba
or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack run-time error '1004' unable to get the vlookup property of the worksheetfunction class 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 VBA - Excel : Vlookup crashes my http://stackoverflow.com/questions/18063214/how-to-error-handle-1004-error-with-worksheetfunction-vlookup program when no match found up vote 4 down vote favorite In my program, the user types a Zip Code and gets as an output information related to the Zip Code (province, city, district). To do this, I use the Vlookup function. So, the user : Types a Zip code in the main sheet The program search in a database (in another sheet) in which Zip Code http://stackoverflow.com/questions/17100782/vba-excel-vlookup-crashes-my-program-when-no-match-found are associated to City, Province, District. When there is a match, it sends the result to the main pages, so the user can get a city, province, district just by typing the Zip Code. Quite simple process. I use this code to do so : If Range("J9").Value <> "N/A" Then 'if there is actually a zip code entered by the user (if not, it will be "N/A") cityZip = Application.WorksheetFunction.VLookup(sMain.Range("J9").Value, sZipCodes.Range("B2:E864"), 3, False) barangayZip = Application.WorksheetFunction.VLookup(sMain.Range("J9").Value, sZipCodes.Range("B2:E864"), 2, False) provinceZip = Application.WorksheetFunction.VLookup(sMain.Range("J9").Value, sZipCodes.Range("B2:E864"), 4, False) sMain.Range("J7").Value = provinceZip sMain.Range("J13").Value = cityZip sMain.Range("J16").Value = barangayZip Else End If It works perfectly when there is a Zip Code which is in my database. But if not, it crashes the execution of the program and I have an error message (like "execution error '1004', unable to read the Vlookup ...). How to modify my code to just say that if there is no match, then it should just do nothing? I don't know how to introduce this request in a Vlookup function. Thanks in advance ! EDIT : here is my new code, after following Tim Williams suggestion : 'Using Zip Code If Range("J9").Value <> "N/A" Then provinceZip = Application.Lookup(sMain.Range("J9").Value, sZipCodes.Range
Forums Excel Questions IsNA vlookup error in VBA need help Page 1 of 2 12 Last Jump to page: Results 1 to 10 of 18 IsNA vlookup error in http://www.mrexcel.com/forum/excel-questions/391500-isna-vlookup-error-visual-basic-applications-need-help.html VBA need helpThis is a discussion on IsNA vlookup error in VBA need help within the Excel Questions forums, part of the Question Forums category; I have this code that does a simple vlookup and populates the values (just like the formula in excel) HOWEVER, ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread vba vlookup Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode May 20th, 2009,02:36 PM #1 abhid Board Regular Join Date May 2006 Posts 50 IsNA vlookup error in VBA need help I have this code that does a simple vlookup and populates the values (just like the formula in excel) HOWEVER, it goes line by line in VBA and excel vba vlookup one value had an #N/A and the code broke. How do I get it to continue and if it does not find the vlookup it either puts and #N/A or colors it red or anything to highlight an #n/a and continues to populate the file. any help!!!! thanks code below. "Query from Qc Report_1" is the column with ID#'s and next to it I want a vlookup done to "apps" where the data is Worksheets("Query from Qc Report_1").Cells(rc, 23).Value = Application.WorksheetFunction.IsNA(WorksheetFunction.VLookup(Worksheets("Query from Qc Report_1").Cells(rc, 22), Range("apps"), 9, False) where do I put the if-then stmt so if it is N/A then put "not found" and continue down the lines. Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote May 20th, 2009,02:41 PM #2 Jonmo1 MrExcel MVP Join Date Oct 2006 Location Bryan, TX Posts 41,806 Re: IsNA vlookup error in VBA need help Errors in worksheet functions are handled much differently in VBA than in a formula.... If it's NA, the code is going to break and go into Debug no matter what you do.. Except, if you drop the worksheetfunction... Here's an example of handling worksheetfunction errors... X = Application