Subscript out of Range error

**lloydgodin** · 03-06-2013, 11:51 AM

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

**Norie** · 03-06-2013, 12:07 PM

I get a Select method of range... error not subscript out of range.

If I change the loop to this, no error...

            For Each x In ActiveWorkbook.Sheets(vData(n, 2)).Range("A1:" & vData(n, 3) & "100").Cells

**lloydgodin** · 03-06-2013, 12:11 PM

I am not even getting the error anymore. I'm not sure why or where...

And here is the corrected attachment.

**lloydgodin** · 03-06-2013, 12:19 PM

And now I AM getting the same error that you did.
When I changed to your code I get the SUBSCRIPT OUT OF RANGE error again.

**lloydgodin** · 03-06-2013, 01:02 PM

Since 'ref' is contained in an add-in I thought the section of code causing the problem might be this:

    vData = ThisWorkbook.Worksheets("Ref").Range("ref_table").Value
    Set wks = ActiveSheet
    sName = wks.Parent.Name

Other sections that reference it work.

**Norie** · 03-06-2013, 01:07 PM

Did you try the change I suggested?

**lloydgodin** · 03-06-2013, 01:26 PM

The add-in cannot be attached but it is a file that contains the above code in a module, the tab REF and the following code in ThisWorkbook:

Const msoControlPopup As Long = 10
Const msoControlButton As Long = 1



Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("TEST CONTROLS v1.0").Delete

End Sub

Private Sub Workbook_Open()
    Dim cmbBar As Object
    Dim cmbControl As Object
     
    Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
    Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True)
    With cmbControl
        .Caption = "&TEST CONTROLS v1.0"
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "RESET REVIEW"
            .OnAction = "ResetReview"
            .FaceId = 1098
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "PRINT REVIEW"
            .OnAction = "PrintReview"
            .FaceId = 1098
        End With
    End With
End Sub

**lloydgodin** · 03-06-2013, 01:08 PM

Yes. And I get the subscipt out of range error still.

**Norie** · 03-06-2013, 01:32 PM

It probably is some sort of reference problem that's causing the error(s).

Hard to tell/test though without the add-in.

**lloydgodin** · 03-06-2013, 01:34 PM

The site wont let me upload it. However, it can be created with the 11:26 post and last attachment has the "REF" tab.

**Norie** · 03-06-2013, 02:25 PM

Sorry, 11:26 post?

Could you post it's number?

PS Are the 2 files you uploaded different?

**lloydgodin** · 03-06-2013, 02:34 PM

Post #8. 11:26 was the time on it b/c I didn't see the post numbers.

The file in post #3 contains the REF tab whereas post 1 did not.

**Norie** · 03-06-2013, 02:38 PM

Post #8 was at 5:26 pm GMT.

I'll have a look at the file later.

You say it's an addin?

Do you have a sample workbook to work with it, or will a blank one do?

**lloydgodin** · 03-06-2013, 02:45 PM

Different time zones, LOL.

Yes the ResetTest.xls is the sample workbook and using the code from previous posts and the REF tab from ResetTest you can create the add-in. It more accurately reflects how I am doing things on this end.

**Norie** · 03-06-2013, 02:47 PM

So what should I use as a sample workbook?

**lloydgodin** · 03-06-2013, 02:55 PM

the ResetTest.xls is the sample workbook

DOesn't matter what you put into as long as you insert lines at 13 and fill it with garbage data. And where it says FACS# put something else is.

**lloydgodin** · 03-11-2013, 11:17 AM

Threadbump

**lloydgodin** · 03-11-2013, 11:44 AM

I think it has to do with the variant 'n.' It's running it 101 times and after the data is empty it'll give the subscript out of range error.

**lloydgodin** · 04-05-2013, 01:36 PM

bumping this again.

**xladept** · 04-05-2013, 02:00 PM

Hi Lloyd,

I was playing with it too and this worked for me:

ActiveWorkbook.Sheets(CStr(vData(n, 2))).Range("A1:" & vData(n, 3) & "100").Select

**lloydgodin** · 04-05-2013, 02:18 PM

Thank XL. I'm actually in the office (Camarillo, CA) and the files are back in Texas on my computer. I will try it when I get back home and let you know. Thank you again.

**xladept** · 04-05-2013, 03:03 PM

You're welcome!

**lloydgodin** · 04-09-2013, 02:12 PM

Nothing I am doing is working. So I zipped the addin, as well as an actual spreadsheet it would be used on.

**xladept** · 04-09-2013, 03:33 PM

Hi Lloyd,

I altered the add in code a little and it ran - see if it's good enough:

For n = LBound(vData) To UBound(vData)
        If vData(n, 1) <> "" Then
        If InStr(sName, vData(n, 1)) Then
        ActiveWorkbook.Sheets(CStr(vData(n, 2))).Select
            ActiveWorkbook.Sheets(CStr(vData(n, 2))).Range("A1:" & vData(n, 3) & "100").Select
          
            For Each x In ActiveWorkbook.Sheets(vData(n, 2)).Range("A1:" & vData(n, 3) & "100").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: End If
    Next n

**lloydgodin** · 04-10-2013, 08:02 AM

If you could highlight the changes and explain why that would be helpful to me. It is working perfectly. Thank you.

**xladept** · 04-10-2013, 01:29 PM

Hi Lloyd,

I noticed that it was choking when the ref was null - so I just told it to ignore null ref records:

For n = LBound(vData) To UBound(vData)
        If vData(n, 1) <> "" Then
        If InStr(sName, vData(n, 1)) Then
        ActiveWorkbook.Sheets(CStr(vData(n, 2))).Select
            ActiveWorkbook.Sheets(CStr(vData(n, 2))).Range("A1:" & vData(n, 3) & "100").Select
          
            For Each x In ActiveWorkbook.Sheets(vData(n, 2)).Range("A1:" & vData(n, 3) & "100").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: End If
    Next n

Oh! and since the code uses the select feature for the range I Activated the Sheet.

**lloydgodin** · 04-10-2013, 01:32 PM

OK. I can see that now. Not sure why it was returning null references though.
Also what did the CStr() do?

I'm pretty new to programming and hardly use what I've learned.

**xladept** · 04-10-2013, 01:45 PM

Hi Lloyd,

That's a pretty sophisticate system for a newcomer - the CStr renders the worksheet name reference into a string and it's necessary because worksheet names are strings

**lloydgodin** · 04-16-2013, 03:49 PM

Well I have had help from several people on here to get it to do what I want it to do.

I did notice 1 error a week later. There are other workbooks that contain several worksheets (as opposed to the ER which contains just "emergency room"). The bill analysis clears ALL worksheets in a single workbook. Not really a problem except it only clears them and does not do the final step of putting "FACS#" into the cell and selecting the same. It only does that on the worksheet it's used on.

**xladept** · 04-16-2013, 05:29 PM

Hi LLoyd,

Can you post the code or, even better, a sample spreadsheet with the fault??

**lloydgodin** · 04-17-2013, 08:22 AM

I figured it out. The code you had provided went through the whole array n=lBound to uBound and used any worksheet name data contained in vData(n,2). All 100 worksheets. So I replaced

       ActiveWorkbook.Sheets(CStr(vData(n, 2))).Select
            ActiveWorkbook.Sheets(CStr(vData(n, 2))).Range("A1:" & vData(n, 3) & "100").Select
          
            For Each x In ActiveWorkbook.Sheets(vData(n, 2)).Range("A1:" & vData(n, 3) & "100").Cells

with

        ActiveWorkbook.Sheets(wks.Name).Select
            ActiveWorkbook.Sheets(wks.Name).Range("A1:" & vData(n, 3) & "100").Select
          
            For Each x In ActiveWorkbook.Sheets(wks.Name).Range("A1:" & vData(n, 3) & "100").Cells

Where wks = Activesheet so it only runs the code if the sheet is Active which is what I needed it to do.

**xladept** · 04-17-2013, 12:05 PM

Good! - Glad you got it!

Subscript out of Range error

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Re: Subscript out of Range error

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions