Application Worksheetfunction Vlookup Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and application worksheetfunction vlookup vba policies of this site About Us Learn more about Stack Overflow the application worksheetfunction countif company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users
Application Worksheetfunction Sumif
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
Excel Vba Vlookup Error 1004
a minute: Sign up Application.WorksheetFunction.VLookup No Match error up vote 1 down vote favorite If Not IsError(Application.WorksheetFunction.VLookup(SRange(z, lastC), sWks.range("$D:$G"), 3, False)) Then or cells(1,1) = Application.WorksheetFunction.VLookup(SRange(z, lastC), sWks.range("$D:$G"), 3, False) or On Error Resume Next m1 = Application.WorksheetFunction.VLookup(SRange(z, lastC), sWks.range("$D:$G"), 3, False) If m1 > 0 Then Cells(e, 3).Value = "Yes" Else Cells(e, 3).Value = "No" End If Alway return Run-time excel vba vlookup error 2042 error ’1004′: Unable to get the VLookup property of the WorksheetFunction class if do not match the entry How to catch this error or solve another way this problem? excel excel-vba error-handling vlookup share|improve this question asked May 19 '14 at 10:04 user3588043 81111 1 Just use Application.VLookup rather than Application.WorksheetFunction.VLookup –Rory May 19 '14 at 10:37 add a comment| 1 Answer 1 active oldest votes up vote 3 down vote accepted Just use Application.VLookup rather than Application.WorksheetFunction.VLookup The WorksheetFunction.function_name versions always return a run-time error if they fail, whereas the Application.function_name versions return an error value that you can test. share|improve this answer answered May 19 '14 at 11:37 Rory 16.1k21219 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 answer, you agree to the privacy policy and terms of service. Not the answer you're looking for? Browse other questions tagged excel excel-vba error-handli
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
Excel Vba Vlookup #n/a
about Stack Overflow the company Business Learn more about hiring developers or posting ads vba vlookup not working with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow vba vlookup not finding value 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 program when no match found up http://stackoverflow.com/questions/23734917/application-worksheetfunction-vlookup-no-match-error 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 are associated to City, Province, District. When there http://stackoverflow.com/questions/17100782/vba-excel-vlookup-crashes-my-program-when-no-match-found 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("B2:E907"), 4, False) If IsError(provinceZip) = False Then cityZip = Application.Lookup(sMain.Range("J9").Value, sZipCodes.Range("B2:E907"), 3, False) barangayZip = Application.Lookup(sMain.Range("J9").Value, sZipCodes.Range("B2:E907"),
Forums Excel Questions Vlookup VBA error if value not found Results 1 to 4 of 4 Vlookup VBA error if value not foundThis is a discussion on Vlookup VBA error if value not found within the Excel http://www.mrexcel.com/forum/excel-questions/683733-vlookup-visual-basic-applications-error-if-value-not-found.html Questions forums, part of the Question Forums category; hi all, i have two sheets which has thousand lines and i will update it daily so there will increase ... 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 Feb 5th, 2013,12:29 PM #1 sriram170 Board Regular vba vlookup Join Date Jan 2012 Posts 98 Vlookup VBA error if value not found hi all, i have two sheets which has thousand lines and i will update it daily so there will increase in line numbers day by day.. instead vlookup formula i planned to use worksheet function vlookup so that it can save time in calculating the worksheet for more than 10 mins. i tried with below code it was success but excel vba vlookup when the value is not found then it gives me a error "runtime error-1004" below is the vba code. is there any way we can run this without error pls help also Sub test() Dim x As Variant Sheets("sheet1").Select Range("h2").Select Do x = Application.WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, Worksheets("sheet2").Range("A:F"), 6, False) If IsError(x) Then ActiveCell.Value = "N\a" Else ActiveCell.Value = x End If ActiveCell.Offset(1, 0).Activate Loop Until IsEmpty(ActiveCell.Offset(0, -7).Value) End Sub Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Feb 5th, 2013,12:30 PM #2 Jonmo1 MrExcel MVP Join Date Oct 2006 Location Bryan, TX Posts 41,806 Re: Vlookup VBA error if value not found Welcome to the board.. Remove the .WorksheetFunction part, and make it just Application.Vlookup Share Share this post on Digg Del.icio.us Technorati Twitter Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious. Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. Ferris Bueller A.K.A. John Hughes, 1986 Reply With Quote Feb 5th, 2013,08:22 PM #3 sriram170 Board Regular Join Date Jan 2012 Posts 98 Re: Vlookup VBA error if value not found thak you jonmo it worked li