I have the follow code to fill cells in excel one by one and it works the way I want it to but it gives me this error when it runs through the array. How do I fix this error? Thanks

The error is "Subscript out of range. Error: 9"

Dim item As Variant
Dim splitString() As String
Dim finalSplit() As String
i = 1
For Each item In splitString
    finalSplit = Split(splitString(s), ",")
    Cells(i, 1) = finalSplit(0)
    Cells(i, 2) = finalSplit(1)
    Cells(i, 3) = finalSplit(2)
    Cells(i, 4) = finalSplit(3)
    i = i   1
    s = s   1
Next

I checked if finalSplit contains enough values like Thomas said and it worked.This is the new code below.

 Dim item As Variant
    Dim splitString() As String
    Dim finalSplit() As String
    i = 1
    For Each item In splitString
        finalSplit = Split(splitString(s), ",")
If UBound(finalSplit) > 1 Then
        Cells(i, 1) = finalSplit(0)
        Cells(i, 2) = finalSplit(1)
        Cells(i, 3) = finalSplit(2)
        Cells(i, 4) = finalSplit(3)
        i = i   1
        s = s   1
End If
    Next

CodePudding user response：

As other commenters have pointed out, why not add another control variable?

Dim item As Variant
Dim splitString() As String
Dim finalSplit() As String
Dim i As Integer, j As Integer, s As Integer
i = 1
For Each item In splitString
    finalSplit = Split(splitString(s), ",")
    For j = 0 To UBound(finalSplit)
        Cells(i, j   1) = finalSplit(j)
    Next j
    i = i   1
    s = s   1
Next

Be aware that this can loop more than the 4 times you expect. A lazy way to solve this would be to add If j > 3 Then Exit For before Next j

I tested this with the following code (it works!), as I have no idea what splitString() or finalSplit() is in your case:

Sub test()
    Dim finalSplit As Variant
    Dim j As Integer
    finalSplit = Split("1,2,3,4,5", ",")
    For j = 0 To UBound(finalSplit)
        Cells(1, j   1) = finalSplit(j)
        If j > 3 Then Exit For
    Next j
End Sub

CodePudding user response：

Looping Through Elements of Arrays

• An array created by the Split function is always 0-based (even if Option Base 1). Similarly, not quite related, an array created by the Array function is dependent on Option Base unless you use its parent VBA e.g. arr = VBA.Array(1,2,3). Then it is always zero-based.

• Looping through the elements of an array (1D array) is done in the following two ways:

  • For Each...Next

    Dim Item As Variant
    For Each Item In Arr 
        Debug.Print Item
    Next Item
    

  • For...Next

    Dim i As Long
    For i = LBound(Arr) To Ubound(Arr)
        Debug.Print Arr(i)
    Next i
    

• Since we have established that Split always produces a zero-based array, in the second example we could use 0 instead of LBound(Arr):

  `For...Next`
  Dim i As Long
  For i = 0 To Ubound(Arr)
      Debug.Print Arr(i)
  Next i
  

Option Explicit

Sub DoubleSplit()
    
    Const IniString As String = "A,B,C,D/E,F,G,H/I,J,K/L/M,N,O,P,Q,R"
    
    Dim SplitString() As String: SplitString = Split(IniString, "/")
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' be more specific
    ws.Cells.ClearContents ' remove previous data; clears the whole worksheet
    
    Dim FinalSplit() As String
    Dim Item As Variant ' SplitString Control Variable
    Dim r As Long ' Worksheet Row Counter
    Dim f As Long ' FinalSplit Element Counter
    
    ' For Each...Next
    For Each Item In SplitString
        r = r   1
        FinalSplit = Split(Item, ",")
        Debug.Print Join(FinalSplit, ",")
        For f = 0 To UBound(FinalSplit)
            ws.Cells(r, f   1).Value = FinalSplit(f)
        Next f
    Next Item

    r = r   1 ' add an empty row
    
    Dim s As Long ' SplitString Element Counter

    ' For...Next
    For s = 0 To UBound(SplitString)
        r = r   1
        FinalSplit = Split(SplitString(s), ",")
        Debug.Print Join(FinalSplit, ",")
        For f = 0 To UBound(FinalSplit)
            ws.Cells(r, f   1).Value = FinalSplit(f)
        Next f
    Next s

' Results

' A,B,C,D
' E,F,G,H
' I,J,K
' L
' M,N,O,P,Q,R
'
' A,B,C,D
' E,F,G,H
' I,J,K
' L
' M,N,O,P,Q,R

End Sub