VBA Array row_number Loop

[powersp68232]

In the following code I read a CSV file to a string and then separate into an array. When I go to write the array to the worksheet using application.index and specify a row it works as expected and the portion of the array is placed into the range. I am needing to loop through the array and index the next portion and overwrite the previous range. I am trying to use variables row_s and row_e, but the range fills with #NAME?. Is there a way to use variables for the row numbers or a way to pull from the array similar to my current code?

Private Sub CSVtoArray()

    Dim rawData As String, lineArr As Variant, cellArr As Variant, i As Integer
    Dim ubR As Long, ubC As Long, rArray As Long, cArray As Long, row_s As String, row_e As String
    Dim Arr As Variant, Destination As Range, A_Index As Variant, strFile As String

strFile = "C:\***.csv"

    Open strFile For Binary As #1
    rawData = Space$(LOF(1))
    Get #1, , rawData
    Close #1
    
    If Len(rawData) > 0 Then

        'If spaces are delimiters for lines change vbCrLf to " "
        lineArr = Split(Trim$(rawData), vbCrLf)

        ubR = UBound(lineArr) + 1
        ubC = UBound(Split(lineArr(6), ",")) + 1
        ReDim Arr(1 To ubR, 1 To ubC)

        For rArray = 6 To ubR
            If Len(lineArr(rArray - 1)) > 0 Then
                cellArr = Split(lineArr(rArray - 1), ",")
                For cArray = 1 To ubC
                    Arr(rArray, cArray) = cellArr(cArray - 1)
                Next
            End If
        Next
        
        row_s = 1
        row_e = 907

        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Cycle 1"
        Sheets("Cycle 1").Select
        i = 0
        ActiveSheet.Range("A1:F" & 907).Resize(907) = Application.Index(Arr, [Row(row_s:row_e)], Application.Transpose([row(1:6)])) 
''''''''''''''''''''''''''' If I use Application.Index( Arr, [Row(1:907]) this works perfectly, but not able to increment to the next  portion of rows.
        For i = i To 3

        row_s = row_s + 900
        row_e = row_s + 900
        
        ActiveSheet.Range("A7:F" & 907).Resize(907) = Application.Index(Arr, Application.Evaluate([Row(row_s:row_e)]), Application.Transpose([row(1:6)]))
        
        Next i
    
    End If
End Sub

 

[JLGWhiz]

 ActiveSheet.Range("A1:F" & 907).Resize(907) = Application.Index(Arr, [Row(row_s:row_e)], Application.Transpose([row(1:6)]))
''''''''''''''''''''''''''' If I use Application.Index( Arr, [Row(1:907]) this works perfectly, but not able to increment to the next  portion of rows.
        For i = i To 3
        row_s = row_s + 900
        row_e = row_s + 900       
        ActiveSheet.Range("A7:F" & 907).Resize(907) = Application.Index(Arr, Application.Evaluate([Row(row_s:row_e)]), Application.Transpose([row(1:6)]))

Where Row has been changed to red font, should be 'Rows' for correct syntax. When using variables to represent integers as a range the colon has to be enclosed in quort marks and the three elements concatenated, e.g. Rows(row-s & ":" & row_ e)

 

[powersp68232]

 ActiveSheet.Range("A1:F" & 907).Resize(907) = Application.Index(Arr, [Row(row_s:row_e)], Application.Transpose([row(1:6)]))
''''''''''''''''''''''''''' If I use Application.Index( Arr, [Row(1:907]) this works perfectly, but not able to increment to the next  portion of rows.
        For i = i To 3
        row_s = row_s + 900
        row_e = row_s + 900      
        ActiveSheet.Range("A7:F" & 907).Resize(907) = Application.Index(Arr, Application.Evaluate([Row(row_s:row_e)]), Application.Transpose([row(1:6)]))

Where Row has been changed to red font, should be 'Rows' for correct syntax. When using variables to represent integers as a range the colon has to be enclosed in quort marks and the three elements concatenated, e.g. Rows(row-s & ":" & row_ e)

I made the suggested changes, but the range is still filling with #NAME? instead of the array values. I also tried setting the variables as integers with the same result.

 

[JLGWhiz]

The #Name? error flag is an Excel error indicator, not VBA. So apparently formulas in those cells are not finding what the formula is looking for. Hard to tell for sure without being able to see the worksheet.

 

[Fluff]

You cannot use variables with the short form of evaluate (ie the [])
try

ActiveSheet.Range("A7:F" & 907).Resize(907) = Application.Index(arr, Application.Evaluate("Row(" & row_s & ":" & row_e & ")"), Application.Transpose([row(1:6)]))

 

[powersp68232]

You cannot use variables with the short form of evaluate (ie the [])
try

ActiveSheet.Range("A7:F" & 907).Resize(907) = Application.Index(arr, Application.Evaluate("Row(" & row_s & ":" & row_e & ")"), Application.Transpose([row(1:6)]))

This works great!! Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback.