Replace and save remaining string in an array-CodePudding

I want to remove predefined parts of the strings in the following table and save the values in an array. For some reason I get an error stating that I'm outside of the index. The lengths of the strings in the table can vary.

Sub New_1()

Dim i, j, k As Integer
Dim Endings As Variant
k = 0
    
Endings = Array("/A", "/BB", "/CCC", "/DDDD", "/EEEEE")

Dim ArrayValues() As Variant

With Worksheets("table1")

    Dim lastRow As Long:  lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    
    ReDim ArrayValues(lastRow)
        
    For i = lastRow To 1 Step -1
    
        For j = 0 To UBound(Endings)
        
            ArrayValues(k) = Replace(.Range("A" & i), Endings(j), "")
            k = k   1
        
        Next j
        
    Next i
    
End With

End Sub

CodePudding user response：

You're getting out of bounds because your ArrValues is filled up after not even 3 iterations of your "i" since you're adding up your k every j iterations

If you want an array of the cleaned up cells do this instead:

Sub New_1()
    Dim i As Integer, j As Integer, k As Integer
    Dim Endings As Variant
    Dim ArrayValues() As Variant
    Dim lastRow As Long
        
    Endings = Array("/A", "/BB", "/CCC", "/DDDD", "/EEEEE")
    
    With Worksheets("Blad6")
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        ReDim ArrayValues(1 To lastRow) 'Then you don't have an empty ArrayValues(0)
            
        For i = lastRow To 1 Step -1
            For j = 0 To UBound(Endings)
                If j = 0 Then
                    ArrayValues(i) = Replace(.Range("A" & i), Endings(j), "")
                Else
                    ArrayValues(i) = Replace(ArrayValues(i), Endings(j), "")
                End If
            Next j
        Next i
        'Use Array here
    End With

End Sub

CodePudding user response：

If your intent is to create an array in which everything after the / is removed, this might be simpler, using the Split function; and also faster by storing the data to be split in a VBA array, in iterating through that array instead of the worksheet cells.

Option Explicit
Sub New_1()

'in VBA, Long is marginally more efficient than Integer
  Dim k As Long, v As Variant
  Dim dataArr As Variant
  Dim ArrayValues() As Variant

With Worksheets("SHEET7")

'faster to loop through VBA array than worksheet cells
'Note that this will be a 2D array with dimensions starting at 1, not 0
    dataArr = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

'This might be simpler
ReDim ArrayValues(1 To UBound(dataArr, 1))
k = 0
For Each v In dataArr
    k = k   1
    ArrayValues(k) = Split(v, "/")(0)
Next v
    
End Sub