Subscript out of range VBA Excel array

Question

I have this code to eliminate all anomalies in the denormlaized hierarchy table. When I try to run this macro with a hundred rows of records, maybe around 200 to 300, it works just fine. But when I try to run the macro with all of my rows, which is around 18,000 lines, it returns the "Subscript out of range" error. I'm not sure what's wrong with the code 'cause it seems to work fine with hundreds of rows. I'm using MS Excel 2010 version. Any help would be appreciated, thank you very much.

Here's my working code:

    Option Explicit

Sub EliminateAnomaliesDH()
Sheets("Denorm Hier").Select
Range("A1").Select
Dim iCtr As Integer
Dim arr As Variant

iCtr = 2

While Range("B" & iCtr).Value <> ""
arr = Split(Range("B" & iCtr).Value, "[")
arr = Split(arr(1), "]")

Select Case arr(0)
    Case "L1"
        Range("F" & iCtr & ":AB" & iCtr & "").Value = ""
    Case "L2"
        Range("H" & iCtr & ":AB" & iCtr & "").Value = ""
    Case "L3"
        Range("J" & iCtr & ":AB" & iCtr & "").Value = ""
    Case "L4"
        Range("L" & iCtr & ":AB" & iCtr & "").Value = ""
    Case "L5"
        Range("N" & iCtr & ":AB" & iCtr & "").Value = ""
    Case "L6"
        Range("P" & iCtr & ":AB" & iCtr & "").Value = ""
    Case "L7"
        Range("R" & iCtr & ":AB" & iCtr & "").Value = ""
    Case "L8"
        Range("T" & iCtr & ":AB" & iCtr & "").Value = ""
    Case "L9"
        Range("V" & iCtr & ":AB" & iCtr & "").Value = ""
    Case "L10"
        Range("X" & iCtr & ":AB" & iCtr & "").Value = ""
    Case "L11"
        Range("Z" & iCtr & ":AB" & iCtr & "").Value = ""
    Case "L12"
        Range("AB" & iCtr & ":AB" & iCtr & "").Value = ""
End Select

iCtr = iCtr + 1
Wend

Sheets("Instructions").Select
MsgBox "Successfully removed all anomalies of the Denormalized hierarchy Table"
End Sub

Check whether you have sheet "Denorm Hier" at a right place/location . — Charles Stevens
– Charles Stevens, Commented Jan 9, 2014 at 6:26

Siddharth Rout · Accepted Answer · 2014-01-09 07:56:32Z

1

Even though you have not mentioned the line where you are getting an error, it is quite obvious. The error is most probably on the line

arr = Split(arr(1), "]")

And the reason is very simple. Because the cell doesn't have "[" so after split there is no ar(1).

Here is a very simple way to reproduce the error.

Sub sample()
    Dim sString As String
    Dim myar

    sString = "Blah Blah"

    myar = Split(sString, "]")

    myar = Split(myar(1), "[") '<~~ Error here

    Debug.Print myar(0)
End Sub

To ensure that you don't get the error, use INSTR() to check if [ or ] exists and then split it.

For example

    If InStr(1, sString, "]") Then
        myar = Split(sString, "]")
    End If

Followup from comments

I rewrote your code. Is this what you are trying? Please note that I have not tested it so let me know if you get any errors. I have commented the code on the relevant parts as well.

Sub EliminateAnomaliesDH()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long
    Dim tempString As String, sString As String

    Set ws = ThisWorkbook.Sheets("Denorm Hier")

    With ws
        '~~> Get the last row which has data in Col B
        lRow = .Range("B" & .Rows.Count).End(xlUp).Row

        '~~> Loop through cells in column B
        For i = 2 To lRow
            sString = .Range("B" & i).Value

            '~~> Check if the cell has both "[" and "]"
            If InStr(1, sString, "[") And InStr(1, sString, "]") Then
                tempString = Split(.Range("B" & i).Value, "[")(1)
                tempString = Split(tempString, "]")(0)

                '~~> This required so that we do an exact match
                '~~> For example, "  l1", "  l1   ", "   L1" etc
                '~~> becomes "L1"
                tempString = UCase(Trim(tempString))

                Select Case tempString
                    Case "L1": .Range("F" & i & ":AB" & i & "").ClearContents
                    Case "L2": .Range("H" & i & ":AB" & i & "").ClearContents
                    Case "L3": .Range("J" & i & ":AB" & i & "").ClearContents
                    Case "L4": .Range("L" & i & ":AB" & i & "").ClearContents
                    Case "L5": .Range("N" & i & ":AB" & i & "").ClearContents
                    Case "L6": .Range("P" & i & ":AB" & i & "").ClearContents
                    Case "L7": .Range("R" & i & ":AB" & i & "").ClearContents
                    Case "L8": .Range("T" & i & ":AB" & i & "").ClearContents
                    Case "L9": .Range("V" & i & ":AB" & i & "").ClearContents
                    Case "L10": .Range("X" & i & ":AB" & i & "").ClearContents
                    Case "L11": .Range("Z" & i & ":AB" & i & "").ClearContents
                    Case "L12": .Range("AB" & i & ":AB" & i & "").ClearContents
                End Select
            End If
        Next i
    End With
    MsgBox "Successfully removed all anomalies of the Denormalized hierarchy Table"
End Sub

edited Jan 9, 2014 at 7:56

answered Jan 9, 2014 at 7:08

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3175963 Over a year ago

If InStr(1, arr, "]") Then myar = Split(arr, "]") End If Is this correct?

Siddharth Rout Over a year ago

No. Not exactly. :) Let me see if i can update the above post with a complete example

Siddharth Rout Over a year ago

@user3175963: Can you give me an example string and what are you trying to extract from it? Are you trying to extract what is between []?

user3175963 Over a year ago

actually im trying to extract from the Denorm Hier sheet. columns from A to AB

Siddharth Rout Over a year ago

@user3175963: Yes I got that but what are you extracting from the cell? something like "L1" from Blah Blah [L1] Blah Blah

|

Collectives™ on Stack Overflow

Subscript out of range VBA Excel array

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related