Copy sheet into 3D array

spencer_time

Board Regular
Joined
Sep 19, 2019
Messages
55
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
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)

Code:
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

spencer_time

Board Regular
Joined
Sep 19, 2019
Messages
55
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,580
Messages
5,770,972
Members
425,654
Latest member
Kotika

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