Excel Vba Runtime Error 9 Subscript Out Of Range
Contents |
here for a quick overview of the site Help Center Detailed answers runtime error 9 subscript out of range vba access to any questions you might have Meta Discuss the workings runtime error 9 subscript out of range excel 2007 and policies of this site About Us Learn more about Stack Overflow the company Business Learn run time error 9 vba subscript out of range more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow run time error 9 subscript out of range excel 2010 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: Fixing “Runtime-error '9'”: Subscript out of range up vote -1 down vote favorite I have the below code which attempts to open a
Runtime Error 9 Subscript Out Of Range Fix
file name which changes on a daily basis if the sheet is empty. The file name is always named as such: "K:\Shared\Num\Temp\Available_list_" & Year(Now()) & Month(Now()) & Day(Now()) & ".txt" The reason behind this is to automate the file activation instead of copying, pasting, and renaming the file every time. I have received this error when I updated the code to include the above mentioned path. Can someone point to me out where my error is? You may find the code below: Private Sub UserForm_Initialize() Application.ScreenUpdating = False If IsEmpty(A_Regular.Range("A2")) Then Dim TxtPath, TxtName As String TxtPath = "K:\Shared\Num\Temp\Available_list_" & Year(Now()) & Month(Now()) & Day(Now()) & ".txt" TxtName = Year(Now()) & Month(Now()) & Day(Now()) & ".txt" Workbooks.OpenText Filename:=TxtPath, Origin:=437, StartRow:=1, _ DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _ Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _ Other:=True, OtherChar:=",", FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 5), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True '-------------------------------------------------------------------------' ' Setup Sheet ' '-----------------------------------------------
Forums Excel Questions Runtime error '9':Subscript out of range Results 1 to 10 of 10 Runtime error '9':Subscript out of rangeThis is a discussion
Run Time Error 9 Subscript Out Of Range Excel 2007
on Runtime error '9':Subscript out of range within the Excel Questions forums, run time error 9 subscript out of range excel 2013 part of the Question Forums category; ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this subscript out of range vba 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 Apr 3rd, 2004,08:40 PM #1 dmcoffman http://stackoverflow.com/questions/26424403/vba-excel-fixing-runtime-error-9-subscript-out-of-range New Member Join Date Jul 2002 Location Northwest Tennessee Posts 41 Runtime error '9':Subscript out of range Got a problem: When I open more than 1 spreadsheet I get the "Runtime error '9':Subscript out of range" error message. It does not happen opening just one?? My macro(s) are as follows: In This Workbook: ----- Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet.PageSetup http://www.mrexcel.com/forum/excel-questions/80775-runtime-error-9-subscript-out-range.html .LeftFooter = "&10&F" .CenterFooter = "" .RightFooter = "&10PsafetyŠ January 2001" .FitToPagesWide = 1 .FitToPagesTall = 1 End With End Sub ------ In Module1 Sub Auto_Open() Application.DefaultFilePath = CurDir() With Application .DisplayCommentIndicator = xlCommentIndicatorOnly .Calculation = xlCalculationAutomatic .Iteration = True .MaxIterations = 500 .MaxChange = 0.0001 End With ActiveWorkbook.PrecisionAsDisplayed = False Sheets("Average Liq").Select Range("A1").Select End Sub ------ I've tried isolating individual lines, it appears the sheet selection is the culprit, but the recorder provides the same syntax. (Each spreadsheet has the same macros, of course sheet name is unique.) Ideas??? Don Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Apr 3rd, 2004,09:01 PM #2 Tom Urtis MrExcel MVP Join Date Feb 2002 Location San Francisco, California USA Posts 11,068 Re: Runtime error '9':Subscript out of range Try commenting out or deleting this line: Application.DefaultFilePath = CurDir() Then save the workbook, close it, quit Excel, launch Excel, and try opening a few workbooks. Any luck? Share Share this post on Digg Del.icio.us Technorati Twitter Tom Urtis Microsoft MVP - Excel Reply With Quote Apr 3rd, 2004,09:1
your Excel programming skills need a boost? Are you wasting valuable time when you write and troubleshoot your code? Get expert training at the Excel VBA Master Class that will be held in Amsterdam, on October 26-27. This two-day course, led by http://www.contextures.com/excelvbatips.html Jan Karel Pieterse and Tony de Jonker, should quickly pay for itself, in the time that you'll save on your VBA projects. Error Message Debug Button F8 Key Stops Working Project Explorer Folders Missing More Tutorials Error Message Debug Button Occasionally, when you try to run a macro, an error message will appear, such as the "Run-Time Error '9'" Subscript Out of Range" error shown below. On this error message, there are 3 buttons available -- End, Debug subscript out and Help. To stop the macro, click the End button. To get information about the error, click Help. In this case, you would end up on page on the Microsoft website, with a list of possible causes and solutions for the Subscript Out of Range error. To go to the VBE and try to solve the problem, click the Debug button. Debug the Macro In this example, a macro was recorded, to go to a sheet named "Main", then subscript out of select cell C3 on that sheet. When the macro is run, the above error message appears. Here is the VBA code, for the GoToMainSheet macro: Sub GoToMainSheet() ' GoToMainSheet Macro Sheets("Main").Select Range("C3").Select End Sub We'll use the Debug button, to troubleshoot the problem: In the error message, click the Debug button The VBE opens, showing the GoToMain Sheet macro, and a line of code is highlighted in yellow, with a yellow arrow n the margin at the left. Excel showed the error message, because it can't complete the highlighted line of code. Look in the Project Explorer window, and check the list of sheets for the active workbook. There are 3 sheets, named "Intro", "Sheet2" and "Sheet3" -- there isn't a sheet named "Main" We'll stop the debugger, and fix the code. On the VBE Toolbar, click the Reset button The code stops running, and the yellow highlighting disappears. Fix the Code Next, you'll edit the code, to change the sheet name. Solution 1: Not Recommended In the line of code, you could replace "Main" with "Intro", which is the current name of the first sheet in the workbook. However, with that solution, the code could show an error again, if anyone changes the sheet name in the future. Sheets("Intro").Select This is not the best solution Solution 2: Recommended Instead of using the name from the Sheet tab, we'll use the sheet's Code Name.