Copy sheet into 3D array

[spencer_time]

Hello forum,

I need to be able to copy a sheet into a 3D array with the first dimension being values in rows of sheet, the second dimension being values in columns of sheet and the third dimension being set by a variable(sample). I need the data for previous samples (speaking of this same code ran with the "sample" variable for the third dimension being a different value) to stay intact as more sheets are loaded into the array. I have some halfway working code, but I'm still far from finished. Any help or suggestions are appreciated. (the functions are in a separate module in my real spreadsheet)

Sub sheetIntoArray()
    Dim ws As Worksheet, rng As Range
    Dim pts As Long
    Dim Response As String, sample As String, tempSample As Long, db As String
    Dim numRow As Long
    Dim numCol As Long
    Dim dataset() As Variant
    Set ws = ActiveSheet
    numRow = cntRow(ws)
    numCol = cntCol(ws)
    Response = "CEQ"
    Set rng = ws.Range("A1").CurrentRegion ' range equals all cells with content adajecent to A1 in current sheet
        If ws.Name Like "*" & Response & "*" Then ' if sheets name equals *any sample* CEQ *any dB value* then...
            sample = Left(ws.Name, InStr(1, ws.Name, Response) - 1) ' extracts the sample number from the name of the sheet #[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=third]#third[/URL]  dimension should be set equal to this##
            db = Mid(ws.Name, InStr(1, ws.Name, Response) + Len(Response)) ' extracts the dB value from the name of the sheet
        End If
    ReDim dataset(0 To numRow + 1, 0 To numCol + 7, 0 To 255) ' redimensions array to the size of the data in current sheet plus 1 row and 7 columns for misc data when in data processing stage and enough room in third dimension to account for maximum number of samples possible
    dataset = rng.Value2 ' ??sets array equal to the values of array?? but doesn't take into account the third dimension
    
    Debug.Print "Rows = " & numRow ' prints number of rows into immediate window for debug purposes
    Debug.Print "Columns = " & numCol ' prints number of columns into immediate window for debug purposes
    Debug.Print "Sample = " & sample ' prints sample number into immediate window for debug purposes
    Debug.Print "dB = " & db ' prints dB value into immediate window for debug purposes
    
End Sub


Public Function cntRow(ws As Worksheet) As Long
    '----------------------------------------------------
    ' Returns: number of rows with adjacent content
    '
    '           ***EXAMPLE USEAGE***
    'numRow = cntRow(ws)
    'MsgBox "Rows = " & numRow
    '----------------------------------------------------
    cntRow = ws.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).row
End Function


Public Function cntCol(ws As Worksheet) As Long
    '----------------------------------------------------
    ' Returns: number of columns with adjacent content
    '
    '           ***EXAMPLE USEAGE***
    'numRow = cntCol(ws)
    'MsgBox "Columns = " & numCol
    '----------------------------------------------------
    cntCol = ws.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
End Function

 

[spencer_time]

I just posted this problem to excelforum too, it can be found at the following link https://www.excelforum.com/excel-pr...565-copy-sheet-into-3d-array.html#post5207841 I will inform both forums when/if a solution is found.

 

[spencer_time]

I haven't heard from anyone on either forum (here at mrexcel and at excelforum) yet. I will inform both forums when either I find a solution or I get help from someone.