VBA Array row_number Loop

powersp68232

New Member
Joined
Apr 24, 2018
Messages
12
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?


VBA 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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Rich (BB code):
 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

New Member
Joined
Apr 24, 2018
Messages
12
Rich (BB code):
 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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You cannot use variables with the short form of evaluate (ie the [])
try
VBA Code:
ActiveSheet.Range("A7:F" & 907).Resize(907) = Application.Index(arr, Application.Evaluate("Row(" & row_s & ":" & row_e & ")"), Application.Transpose([row(1:6)]))
 
Solution

powersp68232

New Member
Joined
Apr 24, 2018
Messages
12
You cannot use variables with the short form of evaluate (ie the [])
try
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,810
Messages
5,638,483
Members
417,027
Latest member
wlknspc7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top