Results 1 to 32 of 32

Subscript out of Range error

Threaded View

  1. #1
    Forum Contributor
    Join Date
    03-22-2012
    Location
    OR, USA
    MS-Off Ver
    Excel 14/2010
    Posts
    273

    Subscript out of Range error

    In the attached workbook I am getting a "SUBSCRIPT OUT OF RANGE" error on the following line.

    ActiveWorkbook.Sheets(vData(n, 2)).Range("A1:" & vData(n, 3) & "100").Select
    The data is present in 'ref' however, the variant clears out at some point with no explanation. Here is complete code as well.

    Public Sub ResetReview()
    '
    
    
    '******************************* BEGIN VARIABLE DEFINITIONS
    
        Dim x, lnCount As Integer, valid As Boolean
        Dim rdDataRange As Range
        Dim vData       As Variant
        Dim n           As Long
        Dim sName       As String
        Dim wks         As Worksheet
        Dim ref_table   As Range
                
    '******************************* END VARIABALE DEFINITIONS
        Application.StatusBar = "Resetting Fields..."
        Application.ScreenUpdating = False
        
        vData = ThisWorkbook.Worksheets("Ref").Range("ref_table").Value
        Set wks = ActiveSheet
        sName = wks.Parent.Name
        
        For n = LBound(vData) To UBound(vData)
            If InStr(sName, vData(n, 1)) Then
                If vData(n, 4) > 0 Then
                    If Len(Range("B" & vData(n, 4))) > 0 Then
                        Set rdDataRange = ActiveWorkbook.Sheets(vData(n, 2)).Range("A" & vData(n, 4) & _
                        ":" & vData(n, 3) & ActiveWorkbook.Sheets(vData(n, 2)).Cells(Rows.Count, "A").End(xlUp).Row + 1)
                        ActiveWorkbook.Sheets(vData(n, 2)).Range("A" & vData(n, 4)).Select
                        With rdDataRange
                            lnCount = 0
                            Do
                                lnCount = lnCount + 1
                                ActiveCell.Offset(1, 0).Select
                            Loop Until IsEmpty(ActiveCell.Offset(0, 1))
                        End With
                        Rows(vData(n, 4) & ":" & (lnCount + vData(n, 4))).Select
                        Selection.Delete Shift:=xlUp
                    End If
                End If
                    
                If vData(n, 5) > 0 Then
                    If Len(Range("B" & vData(n, 5))) > 0 Then
                        Set rdDataRange = ActiveWorkbook.Sheets(vData(n, 2)).Range("A" & vData(n, 5) & _
                        ":" & vData(n, 3) & ActiveWorkbook.Sheets(vData(n, 2)).Cells(Rows.Count, "A").End(xlUp).Row + 1)
                        ActiveWorkbook.Sheets(vData(n, 2)).Range("A" & vData(n, 5)).Select
                        With rdDataRange
                            lnCount = 0
                            Do
                                lnCount = lnCount + 1
                                ActiveCell.Offset(1, 0).Select
                            Loop Until IsEmpty(ActiveCell.Offset(0, 1))
                        End With
                        Rows(vData(n, 5) & ":" & (lnCount + vData(n, 5))).Select
                        Selection.Delete Shift:=xlUp
                    End If
                End If
    '       End If
    '    Next n
    '
    '    For n = LBound(vData) To UBound(vData)
    '        If InStr(sName, vData(n, 1)) Then
                ActiveWorkbook.Sheets(vData(n, 2)).Range("A1:" & vData(n, 3) & "100").Select
                For Each x In Selection.Cells
                    If x.Locked = False Then
                        If x.MergeCells Then
                            x.MergeArea.Name = "MergedCells"
                            Range("MergedCells").ClearContents
                        Else
                            Select Case x.NumberFormat
                                Case "General"
                                    x.ClearContents
                                Case "0"
                                    x.ClearContents
                                Case "mm/dd/yy;@"
                                    x.ClearContents
                                Case "$#,##0.00_);($#,##0.00)"
                                    x.Value = "0"
                            End Select
                        End If
                    End If
                Next
    '        End If
    '    Next n
    '
    '    For n = LBound(vData) To UBound(vData)
    '        If InStr(sName, vData(n, 1)) Then
                With wks.Range(vData(n, 3) + "1")
                    .Value = "FACS#"
                    Application.Goto .Cells
                End With
                Exit For
            End If
        Next n
        
        ActiveSheet.Calculate
        Application.ScreenUpdating = True
        Application.StatusBar = "Ready"
    End Sub
    Attached Files Attached Files
    Last edited by lloydgodin; 03-06-2013 at 12:06 PM. Reason: corrected attachment

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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