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