Help populating multiple arrays with one for loop

[duggie33]

I apologize if this has been answered elsewhere...I searched for quite a while and found nothing specific for what I am hoping to do.

I am trying to populate multiple 1D arrays with a nested For Next loop. I have tried to use a string to build the array name (ie. arr1, arr2, etc.) and reference the string in place of the array name for the LBound and UBound. I get an error stating "Expected array". Is there a way to do this? Below is my code.

Sub RedimTrials2()


    Dim arr1() As Variant
    Dim arr2() As Variant
    Dim arr3() As Variant
    Dim arr4() As Variant
    Dim str_ArrayName As String
    Dim i As Integer
    Dim j As Integer
    
    i = 3
    
    ReDim arr1(1 To i)
    ReDim arr2(1 To i)
    ReDim arr3(1 To i)
    ReDim arr4(1 To i)
    
    For j = 1 To 4
        str_ArrayName = "arr" & j
        For i = LBound(str_ArrayName, 1) To UBound(str_ArrayName, 1)
            str_ArrayName(i, j) = Sheet1.Range("A1").Offset(i - 1, j - 1).Value
        Next i
    Next j


End Sub

Any help would be greatly appreciated.

Thanks,

duggie33

 

[=ODIN=]

I don’t have my computer in front of me to test this myself, but what if you try the same code except declare str_ArrayName as variant instead of string?

 

[duggie33]

Thanks ODIN, but it did not work. I ended up learning about "jagged arrays" (array of arrays) and it seems to work for what I was hoping to do. It has other limitations with copying the array data to a range but I believe there is a solution out there somewhere. It creates arrays locations like arr(1)(1,1), arr(1)(2,1)....arr(4)(12,1). If I want to copy arr(1) to a range it does not work so the workaround is to loop through the array and copy individual values to cells. The code working is below with random data in A1:D12.

Any ideas on copying jagged arrays to a range? Thanks.

Sub RedimTrials3()


    Dim arr() As Variant
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
    i = 12
    
    ReDim arr(1 To 4)
    
    For j = 1 To UBound(arr)
        arr(j) = Sheet1.Range("A1").Offset(0, j - 1).Resize(i, 1).Value
    Next j
    
    For j = 1 To UBound(arr)
        For k = 1 To i
            Sheet1.Range("H1").Offset(k - 1, j - 1).Value = arr(j)(k, 1)
        Next k
    Next j


End Sub

 

[MickG]

Slight alternative to the second part of your code, possibly :-

For j = 1 To UBound(arr)
       Range("H1").Offset(, j - 1).Resize(i).Value = arr(j)
    Next j

 

[duggie33]

Thanks MickG! It works so I must have screwed-up the syntax...I do that quite a bit as I learn how to do new things in Excel (VBA). I also learned about Application.Transpose which works nicely to transpose arrays and Application.Index to slice arrays.

 

[Rick Rothstein]

Thanks MickG! It works so I must have screwed-up the syntax...I do that quite a bit as I learn how to do new things in Excel (VBA). I also learned about Application.Transpose which works nicely to transpose arrays and Application.Index to slice arrays.

My gut tells me you are overcomplicating whatever it is you want to do, but without more information, I cannot be sure.

What are you ultimately trying to do? What do you have to begin with and what to you ultimately want to do with that data?

 

[duggie33]

edit: fomattig messed-up...

Hi Rick,

Ultimately I plan to implement what I am learning about working with arrays to apply them to a work project. I will be parsing a data acquisition raw data file that includes three columns with anywhere between 500,000 and 1,000,000 rows of data. The data is from a torsion tester so the data includes time, angle, and torque values. The torque is cyclic (read sine wave) and I am looking for the peak torque values that happen approximately every 30 degrees. The data acquisition rate is 500 Hz (2ms) and angular speed is 30 degrees per second. The peaks are not always the same and sometimes the data acquisition can actually get two consecutive readings of the same value that happen to be the peak.

I have a solution that currently loops through a range evaluating each torque value. It is continuously accessing the sheet so I was thinking it was less efficient than it could be so I started learning about arrays. I do a three stage check to make sure I can find the peak values accurately; first is checking to make sure the value is above a certain threshold, second is that there was no other peak found in the previous 50 readings, and third is that the values evaluated is greater than the previous 50 readings and 50 following readings. Three “trues” equals a peak value.

To the point, I am creating an array of the torque values, looping through and evaluating for the peak values, and indicating peaks by putting index value in a column to the right of the torque value. Next step is to create the output which is a table of the time, angle, and torque values for each peak value. I plan on using the ReDim Preserve to build 1D arrays for each attribute as the peaks are found. I learned that you cannot ReDim Preserve and change the Ubound of the “first dimension” (rows)…not sure why but I am certain there is a valid reason. I know that I could have a 3 row, 4000 column output and transpose it but I wanted to try the three separate 1D arrays. At the end I will write the arrays to the output worksheet.

Hopefully that makes sense to someone other than me… I am open to suggestions if there are better ways.

Thanks,

duggie33

 

[Norie]

Are you sure it's multiple 1 dimensional arrays you want?

If you have code that works with a range why not populate an array with that range and then alter the code to work with that rather than the range.

It's pretty straightforward to populate an array from a range, and it's pretty easy to work with the resultant array too.

Dim myArray As Variant
Dim I As Long
Dim J As Long

    myArray = Range("A1:F1000")

    For I = LBound(myArray,1) To UBound(myArray,1) ' loop through the 1st dimension of the array, equivalent to looping through the rows of the range
        For J = LBound(myArray,2) To UBound(myArray,2) ' loop through the 2nd dimension of the array, equivalent to looping through the columns of the range 

              ' do stuff
        Next J
    Next  I

 

[duggie33]

Hi Norie,

Sorry if I was not clear in my explanation. I create an array from theraw data, find the peak torque values, and create the 1D arrays of the outputwhich is the peak values along with the associated time and angle. My output table will typically be between2000 and 5000 rows.

Thanks,

Doug

 

[Norie]

Doug

Do you definitely need 1D arrays for the output?

Where is the output going?

If you do need them do you need them simultaneously?

Can you not populate an array, send it's values to the sheet, re-populate the array with new data, send that to the sheet and so on?