On Error Goto 1004
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 on error skip line vba site About Us Learn more about Stack Overflow the company Business Learn more
Excel Vba Vlookup Error 1004
about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss
Excel Vba On Error Goto Line
Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How to error
Excel Vba Error Handling Best Practice
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 = "Example" cellNum = wsFunc.VLookup(currName, rngLook, 13, False) VLookup is not expected to always find a result; but when it vba error handling in loop 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 31.6k72857 asked Aug 5 '13 at 16:33 JBurace 1,01782851 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(currName, rngLook, 13, 0) If Err.Number <> 0 Then ''error appeared MsgBox "currName not found" ''optional, no need to do anything End If On Error GoTo 0 ''no error, coming back to default conditions End Sub share|improve this answer a
here for a quick overview of the site Help Center Detailed answers to any questions you might have vba on error exit sub Meta Discuss the workings and policies of this site About Us on error goto 0 Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads vba on error goto 0 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 6.2 million programmers, http://stackoverflow.com/questions/18063214/how-to-error-handle-1004-error-with-worksheetfunction-vlookup just like you, helping each other. Join them; it only takes a minute: Sign up Run-time Error 1004; Error handling up vote 0 down vote favorite I've got a problem with my error handling. I'm stitching together a series of numbered files, for multiple business locations. I've written the code to move to the next location http://stackoverflow.com/questions/32506125/run-time-error-1004-error-handling loop on the error of "file not there", because each location has a different number of files. Even with the error handling, I'm getting a Run-time 1004 error. How can this be? It bombs on the second location loop, works on the first. I've used a 15-file loop to be safe, because the max I have encountered is 12. Sub OpenURL() Dim LocBackupFile As String Dim CurrentFile As String Dim HubFileName As String Application.DisplayAlerts = False Filedate = Format(Date, "mm-dd-yyyy") 'Cycle through Hubs HubArray = Array("GA100%20-%20AHUB", "TX100%20-%20DHUB", "CA200%20-%20HHUB", "IN100%20-%20IHUB", "WA100%20-%20KHUB", _ "AB100%20-%20LHUB", "MO100%20-%20MHUB", "NC100%20-%20NHUB", "OH100%20-%20OHUB", "PA100%20-%20SHUB", _ "IN200%20-%20THUB", "UT100%20-%20UHUB", "ON100%20-%20VHUB", "MN100%20-%20WINO", "NL100%20-%20YHUB") For Hub = LBound(HubArray) To UBound(HubArray) HubName = Left(HubArray(Hub), 5) HubFileName = HubName & " NoLocBackup " & Filedate & ".xlsb" For CheckAndOpen = 1 To 15 LocBackupFile = "http://fastnet.--------.com/sites/fastreports/Hubs/" & HubArray(Hub) & "/locbackup_ws" & CheckAndOpen & ".xls" On Error Resume Next Workbooks.Open FileName:=LocBackupFile On Error GoTo Done CurrentFile = "locbackup_ws" & CheckAndOpen & ".xls" If CheckAndOpen = 1 Then RowCount = ActiveSheet.UsedRange.Ro
Error and Resume statements Finding how you can use an error to your advantage When working with VBA, you should be aware of two broad classes of errors: programming errors and run-time errors. (I cover programming errors, also known as bugs, in the next chapter.) A well-written http://what-when-how.com/excel-vba/error-handling-techniques-in-excel-vba/ program handles errors the way Fred Astaire danced: gracefully. Fortunately, VBA includes several tools to help you identify errors — and then handle them gracefully. Types of Errors If you've tried any of the examples in this topic, you http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom/vba-error-1004-when-trying-to-open-an-excel-only/64c71abf-d16c-49e1-9c8f-af76fe14edb2 have probably encountered one or more error messages. Some of these errors result from bad VBA code. For example, you may spell a keyword incorrectly or type a statement with the wrong syntax. If you make such an error, on error you won't even be able to execute the procedure until you correct it. This chapter does not deal with those types of errors. Instead, I discuss runtime errors — the errors that occur while Excel executes your VBA code. More specifically, this chapter covers the following: Identifying errors Doing something about the errors that occur Recovering from errors Creating intentional errors (Yes, sometimes an error can be a good thing.) The ultimate goal of error handling is to on error goto write code that avoids displaying Excel's error messages as much as possible. In other words, you want to anticipate potential errors and deal with them before Excel has a chance to rear its ugly head with a (usually) less-than-informative error message. An Erroneous Example To get things started, I developed a short VBA macro. Activate the VBE, insert a module, and enter the following code: As shown in Figure 12-1, this procedure asks the user for a value. It then enters the square root of that value into the active cell. Figure 12-1: The InputBox function displays a dialog box that asks the user for a value. You can execute this procedure directly from the VBE by pressing F5. Alternatively, you may want to add a button to a worksheet (use the Forms toolbar to do this) and then assign the macro to the button. (Excel prompts you for the macro to assign.) Then you can run the procedure by simply clicking the button. The macro's not quite perfect Enter this code and try it out. It works pretty well, doesn't it? Now try entering a negative number when you are prompted for a value. Oops. Trying to calculate the square root of a negative number is illegal on this planet. Excel responds with the message shown in Figure 12-2, indicating that your code generated a run-time error. For now, just click
be down. Please try the request again. Your cache administrator is webmaster. Generated Sun, 23 Oct 2016 13:00:31 GMT by s_wx1085 (squid/3.5.20)