Link to home
Start Free TrialLog in
Avatar of zack carter
zack carter

asked on

Advice on Code Failing to function

eThe below code is meant to split row data that contains a special character "/" within the cell data for example "SEL/EHL" and populate single value into the row below by having row only for "SEL" and a duplicated row with the cell now with the "EHL" value and to remove the "/" from the metadata that was split.

However the code below for "&" works fine but the above fails to run and generates a run time 9 error message "Subscript out of range" message.

Can any of my peers advise why its happening on this "/" piece of code and not the "&" part of the code even though they are meant to be doing the same thing.

Option Explicit

Sub clean_pos_data()
    Dim rawData() As Variant
    Dim startRange As Range
    Dim v As Long
    Dim i As Long
    Dim j As Long
    Dim x As Long
    Dim cleanData() As Variant
    Dim splitField() As String
    
    With ws_PosSeq
        Set startRange = .Range("a3")
        rawData = startRange.Resize(.UsedRange.Rows.count - startRange.Row, .UsedRange.Columns.count).Value
    End With
    
    For i = 1 To UBound(rawData, 1)
        If InStr(1, rawData(i, 9), "&") > 0 Then
            splitField = Split(CStr(rawData(i, 9)), "&")
            For x = 0 To UBound(splitField)
                 v = v + 1
            Next x
        Else
            If InStr(1, rawData(i, 9), "&") > 0 Then
                splitField = Split(CStr(rawData(i, 9)), "&")
                For x = 0 To UBound(splitField)
                    v = v + 1
                Next x
            End If
        End If
    Next i
    
    ReDim cleanData(1 To UBound(rawData, 1) + v, 1 To UBound(rawData, 2))
    v = 0
    
    For i = 1 To UBound(rawData, 1)
        If InStr(1, rawData(i, 9), "&") > 0 Then
            splitField = Split(CStr(rawData(i, 9)), "&")
            For x = 0 To UBound(splitField)
                For j = 1 To UBound(rawData, 2)
                    cleanData(i + x + v, j) = rawData(i, j)
                    cleanData(i + x + v, 9) = Trim(splitField(x))
                Next j
            Next x
            v = v + UBound(splitField)
        Else
            If InStr(1, rawData(i, 9), "/") > 0 Then
                v = v + 1
                splitField = Split(CStr(rawData(i, 9)), "&")
                For x = 0 To UBound(splitField)
                    For j = 1 To UBound(rawData, 2)
                        cleanData(i + x + v, j) = rawData(i, j)
                        cleanData(i + x + v, 9) = Trim(splitField(x))
                    Next j
                Next x
                v = v + UBound(splitField)
            Else
                For j = 1 To UBound(rawData, 2)
                      cleanData(i + v, j) = rawData(i, j)
                Next j
            End If
        End If
    Next i
    
    cleanData = clean_array(cleanData)
    
    ws_PosSeq.Range("a3").Resize(UBound(cleanData, 1), UBound(cleanData, 2)).Value = cleanData
    
End Sub

Open in new window

Avatar of Rgonzo1971
Rgonzo1971

HI,

Are you sure of line 25 since line 19
           If InStr(1, rawData(i, 9), "&") > 0 Then

Open in new window

and line  50
                splitField = Split(CStr(rawData(i, 9)), "&")

Open in new window

Regards
I think rgonzo has identified the cause of your error.

Here is a simplification to your resize calculation:
Change these For...Next loops:
            For x = 0 To UBound(splitField)
                 v = v + 1
            Next x

Open in new window

into
                 v = v + UBound(splitField) +1

Open in new window

I don't see a need to loop to get an accurate calculation.

A further simplification can be achieved if you use the regular expression (regexp) object.
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I assume from the way the code is written that you may have multiple delimiters (or none) in that field.
I also assume that there can only be one kind of delimiter (& or /) in that field at a given time.  That is, the two different delimiters can not appear in the field at the same time.
Avatar of zack carter

ASKER

Thank you for your help all the solutions resolved my issue.

I couldn't have done it without your help or in the time it has taken you to help me!