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

 

[duggie33]

My thought was (remember that I am relatively new to VBA) to build the output arrays as the peaks are found and then sending the whole array to the sheet at once (or much less than 6,000 to 20,000 times) would be faster than multiple sends to the sheet. I have no reference to how much time that will save. I have only read that accessing the sheet is slower than working within an array.

I currently have a working version which finds the peak values within the array and writes them individually to the sheet. I am not saying that it is slow because it crunches a whole bunch of data much, much faster than the ways I used to do this with hundreds of thousands of formulas that would nearly crash my computer.

If my line of thinking is sending me down a wrong path (I learn a lot on those paths) I am more than willing to listen, try, learn.

Thanks for your time!

Doug

 

[Akuini]

Could you post your sample data as table?
And explain what you're trying to do using that sample data. That will make it easier to understand the problem.

 

[duggie33]

I am sorry, I cannot supply any of my actual work test data on a public forum and I do not have a "sample" data set readily available. I have my code working for with a couple bugs to work out. Redim Preserve on a the sub-arrays of a jagged array is a bit convoluted...

Thank everyone for your kindness and help.

Doug