Results 1 to 8 of 8

Run time error '9': Subscript out of Range

Threaded View

  1. #1
    Registered User
    Join Date
    06-24-2015
    Location
    Wisconsin
    MS-Off Ver
    Excel 2013
    Posts
    49

    Run time error '9': Subscript out of Range

    I"m getting the following error on my VBA:

    Run time error '9': Subscript out of Range.

    I'm sure it has something to do with the fact that for the data that I"m dealing with there is not a WS2 which holds "Y" because this data doesn't have Y values. I Need to figure out how to have the process ignore if there is no WS2 or WS1 to not error out.
    Sub FormatSave()
    
    'Convert to format desired
    Dim WS1, WS2 As Worksheet
    Dim Rand As Long
    Set WS1 = Worksheets("N")
    Set WS2 = Worksheets("Y")
    Dim lRow1, lRow2 As Long
    Dim rcell As Range
    
    'lRow1 = N.Range("A" & Rows.Count).End(xlUp).Row
    'lRow2 = Y.Range("A" & Rows.Count).End(xlUp).Row
    
    Set rcell = Columns(1).Find("Steps", LookIn:=xlValues, lookat:=xlWhole)
        If Not rcell Is Nothing Then
            Rows(rcell.Row).Resize(12).Delete  'CAN REPLACE .Hidden with .Delete if that suits you better
        End If
    Set rcell = Nothing
    
    If Not WS1 Is Nothing Or Not WS1 Is Empty Then
    With WS1
    
        ' Delete first row
        .Range("A1:G1").Delete
        
        ' CustomerID
        .Range("A:A").ColumnWidth = 7
        
        ' Amount
        .Range("B:B").ColumnWidth = 18
        
        ' Item Type
        .Range("C:C").ColumnWidth = 12
        
        ' Reference Nbr
        .Range("D:D").ColumnWidth = 30
        
        ' Accounting Date
        .Range("E:E").ColumnWidth = 8
        
        ' Term
        .Range("F:F").ColumnWidth = 4
        
        ' Reversal Ind
        .Range("G:G").ColumnWidth = 1
    
        
    
    ESTFileDate = Format(Now, "MMDDYYYY")
    
    ' Save File as XLS
        ChDir "\FA_files"
        .SaveAs Filename:= _
            "\FA_files\DisbPos_" & ESTFileDate & ".xls", FileFormat:= _
            xlPrinter, CreateBackup:=False
    
            
    End With
    
    End If
    
    If Not WS2 Is Nothing Or Not WS2 Is Empty Then
    
    With WS2
    
        ' Delete first row
        .Range("A1:G1").Delete
        
        ' CustomerID
        .Range("A:A").ColumnWidth = 7
        
        ' Amount
        .Range("B:B").ColumnWidth = 18
        
        ' Item Type
        .Range("C:C").ColumnWidth = 12
        
        ' Reference Nbr
        .Range("D:D").ColumnWidth = 30
        
        ' Accounting Date
        .Range("E:E").ColumnWidth = 8
        
        ' Term
        .Range("F:F").ColumnWidth = 4
        
        ' Reversal Ind
        .Range("G:G").ColumnWidth = 1
        
    
    ESTFileDate = Format(Now, "MMDDYYYY")
    
    ' Save File as XLS
        ChDir "\FA_files"
        .SaveAs Filename:= _
            "\FA_files\DisbNeg_" & ESTFileDate & ".xls", FileFormat:= _
            xlPrinter, CreateBackup:=False
    
            
    End With
    
    End If
    
    With Sheet1
    
    ' Save File as XLS
        ChDir "\FA_files"
        .SaveAs Filename:= _
            "\FA_files\DisbAll_" & ESTFileDate & ".xls", FileFormat:= _
            xlPrinter, CreateBackup:=False
    
    End With
    
    End Sub
    Last edited by alansidman; 09-30-2016 at 11:42 AM. Reason: code tags added

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Run-time error 9 subscript out of range
    By LeonHeart93 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-29-2016, 03:44 AM
  2. [SOLVED] Run-time error '9': subscript out of range - error occurs on multiple computers except one
    By BrettE in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-06-2014, 11:19 PM
  3. Run-time error 9: Subscript out of Range
    By boesingen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-14-2013, 09:41 AM
  4. [SOLVED] Run time error 9 subscript out of range
    By cgabe001 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-05-2012, 01:10 PM
  5. run time error 9 : subscript out of range
    By baxtercavendish in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 08-28-2009, 11:14 AM
  6. [SOLVED] run-time error '9': Subscript out of range
    By AccessHelp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-30-2005, 01:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1