Replace and save remaining string in an array

Question

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

That's not what's causing the error here, it's his k=k+1 being inside the j iteration and for what I can see in my vba, it starts at 0, not 1. — Notus_Panda
– Notus_Panda, Commented Dec 1, 2022 at 13:47
Your for j ... loop is storing four items in sequential elements of ArrayValue for each entry on your worksheet. You probably only want to store a single value. — Ron Rosenfeld
– Ron Rosenfeld, Commented Dec 1, 2022 at 13:51
You should place k = k + 1 after the loop ending with Next j. I suppose that you need a single value obtained by replacing of all Endings array elements. Is that supposition correct? — FaneDuru
– FaneDuru, Commented Dec 1, 2022 at 13:55
Why do you iterate backwards? Then, at the code end you should use Redim Preserve ArrayValues(k -1). To avoid having a last empty element. Depending on what you intend doing with the array, it may stay there, but usually is good to have an array without empty elements... — FaneDuru
– FaneDuru, Commented Dec 1, 2022 at 14:07

Notus_Panda · Accepted Answer · 2022-12-01 14:00:20Z

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

Ron Rosenfeld · Accepted Answer · 2022-12-01 14:14:39Z

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

Collectives™ on Stack Overflow

Replace and save remaining string in an array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related