+ Reply to Thread
Results 1 to 21 of 21

Copy sheet into 3D array

  1. #1
    Registered User
    Join Date
    08-27-2019
    Location
    Alabama, USA
    MS-Off Ver
    365, 2016, 2010, 2007
    Posts
    57

    Copy sheet into 3D array

    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 number). 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)

    Please Login or Register  to view this content.
    p.s. I posted this problem to mrexcel forum earlier today at the following link https://www.mrexcel.com/forum/excel-...-3d-array.html I will inform both forums when/if I find a solution.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,250

    Re: Copy sheet into 3D array

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Alan


    Have you debugged your code?
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    08-27-2019
    Location
    Alabama, USA
    MS-Off Ver
    365, 2016, 2010, 2007
    Posts
    57

    Re: Copy sheet into 3D array

    It was too large to upload to this site, but I saved it to my google drive.

    This zip file contains my macro spreadsheet I'm working on, one of my reports that I hope to automate once the macro spreadsheets is complete, and some sample .s2p files (fancy .txt files) to aid in testing and debugging the macro spreadsheet.

    https://drive.google.com/uc?export=d...34bVnMBJ8i7gvB


    EDIT16:54CST: Just to clairify, the overall goal of this portion of the program is to copy the data imported from "text" files (they have a .s2p extension, but that is the only difference) from multiple samples that are on multiple sheets and have all the data in an array {formatted like array(rows, columns, sample#)}
    Last edited by spencer_time; 10-09-2019 at 05:55 PM.

  4. #4
    Registered User
    Join Date
    08-27-2019
    Location
    Alabama, USA
    MS-Off Ver
    365, 2016, 2010, 2007
    Posts
    57

    Re: Copy sheet into 3D array

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

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,250

    Re: Copy sheet into 3D array

    I believe that you have made your request to bulky. I would urge you to not supply all your files in totality. To much for anyone interested in helping to absorb. Upload to this site a small sample that is representative of your data and then from that what your expected results for that sample looks like. Help us to help you by making it easy for us. We are all volunteers here with a life apart from this forum with limited time to help. If your needs require a lot of research, then many (like me) will just walk away.

  6. #6
    Registered User
    Join Date
    08-27-2019
    Location
    Alabama, USA
    MS-Off Ver
    365, 2016, 2010, 2007
    Posts
    57

    Re: Copy sheet into 3D array

    For this chunk of the problem I am just trying to copy sheets into an array where dimension one of the array is what sheet(what I called sample in my earlier post) is being stored, dimension two corresponds to what row the data is on and dimension three corresponds to what column the data is on.

    The array would look something like the following:
    Please Login or Register  to view this content.
    Where I could loop through each sheet and copy the data from said sheet into the row(2) and column(3) of the array.

    Although it doesn't work, I envisioned something like the following:
    Please Login or Register  to view this content.
    I hope that was a little more clear, I'm still learning, and I don't know the best way to explain (or do) things yet.

    Thank you for your advice so far.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,619

    Re: Copy sheet into 3D array

    Why not use a dictionary?

    You could use the shee names for keys and the values could be arrays with the data from the sheets
    If posting code please use code tags, see here.

  8. #8
    Registered User
    Join Date
    08-27-2019
    Location
    Alabama, USA
    MS-Off Ver
    365, 2016, 2010, 2007
    Posts
    57

    Re: Copy sheet into 3D array

    I've never used a dictionary, I will look into it and see about it.

    I've been playing with getting a 3D array defined with sheet, row, column most of the day, and I feel like I'm close, but just can't get the syntax correct.
    This is what I have so far:
    Please Login or Register  to view this content.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,619

    Re: Copy sheet into 3D array

    Why a 3-d array?

  10. #10
    Registered User
    Join Date
    08-27-2019
    Location
    Alabama, USA
    MS-Off Ver
    365, 2016, 2010, 2007
    Posts
    57

    Re: Copy sheet into 3D array

    So I can keep track of which sample is which while I am performing math on them all, they will all then be copied back to sheets and plotted.

    I'm not opposed to your suggestion of a dictionary, I just don't know what they are and how to use them. I will have to read about them a bit.

    EDIT 16:21CST: I have a bit of experience with 1D array and 2D array, but this is the first 3D array I've tried and it is more difficult as I cant just copy a row to the array or a sheet to the array due to the extra dimension.

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,619

    Re: Copy sheet into 3D array

    This code will create a dictionary, dicDataSets, with data from each sheet in a workbook.
    Please Login or Register  to view this content.
    The data from each sheet will consist of a 2-dimensional array populated from the range A1:L1601.

    To access the data for a sheet use dicDataSets(sheetname).

  12. #12
    Registered User
    Join Date
    08-27-2019
    Location
    Alabama, USA
    MS-Off Ver
    365, 2016, 2010, 2007
    Posts
    57

    Re: Copy sheet into 3D array

    Thank you Norie, I can't test it right now, but I will first thing in the morning.

    How would I look at a single value out of the dictionary (If I for instance wanted to see the value at C7 on a sheet named 01CEQ01 that was stored into the dictionary)?

    How would I edit/modify an entry in the dictionary?

    How would I write an entry back into another sheet once I have done all the required calculations on the data?

    EDIT 18:08CST: Edited formatting to improve readability.
    EDIT 18:11CST: The suspense is killing me, I'm going to set up a computer and install office so I can test this tonight, I've been working on the project that this small piece belongs to for over a week now and can't wait to complete it!
    Last edited by spencer_time; 10-10-2019 at 07:12 PM. Reason: formatting

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,619

    Re: Copy sheet into 3D array

    Like I said you can access the data for a sheet using dicDataSets(sheetname).

    So to get the data from a sheet named '01CEQ01' we can use this,
    Please Login or Register  to view this content.
    and we can get the value from C7 like this.
    Please Login or Register  to view this content.
    To change the value you can use something like this,
    Please Login or Register  to view this content.
    and we can update the data for the sheet '01CEQ01' in the dictionary like this.
    Please Login or Register  to view this content.
    Finally to return the data to the sheet.
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    08-27-2019
    Location
    Alabama, USA
    MS-Off Ver
    365, 2016, 2010, 2007
    Posts
    57

    Re: Copy sheet into 3D array

    Well, I was able to test it out with some data in a similar format as the end use and it appears to work.

    However, the dictionary disappears when the sub is complete, is there a way to make it persevere until manually deleted or cleared?

    Thank you for your help thus far, you have been more helpful than everyone else, and to add to that you taught me of a feature that I've never used before...dictionaries.

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,619

    Re: Copy sheet into 3D array

    Any variable declared within the sub would disappear once the code is finished, including your 3-d array.

    Where else do you want to access/use the data?

  16. #16
    Registered User
    Join Date
    08-27-2019
    Location
    Alabama, USA
    MS-Off Ver
    365, 2016, 2010, 2007
    Posts
    57

    Re: Copy sheet into 3D array

    By another piece of code that a different button runs, but if that is the case then I can load into dictionary, do all calculations, rearrange in correct order, resave to sheets, and then pull data back off from sheets when(if) I need to access it again.

    I'm going to try to shift some things around in my flow chart to keep that from being a need, I wont know until I get back to work in the morning.

    Thanks again for all of your help!

  17. #17
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,619

    Re: Copy sheet into 3D array

    Why not pass the dictionary to the other code?

    Or, and probably better, create a function that creates, and returns, the dictionary.

    Please Login or Register  to view this content.
    You could call this function from other code like this.

    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    08-27-2019
    Location
    Alabama, USA
    MS-Off Ver
    365, 2016, 2010, 2007
    Posts
    57

    Re: Copy sheet into 3D array

    Yes, I like that idea a lot. I can even pass the number of points to it that way so it isn't hard coded in case I ever get data from someone else that has a different number of points.
    Please Login or Register  to view this content.
    It will take me the better half of today to get this incorporated in my spreadsheet and rearrange a few things, but I will let you know when I'm done how it turns out....I'm optimistic, this feels like a good solution.

    Thank you for all of your help and time, I never would have arrived at this solution without your help.

  19. #19
    Registered User
    Join Date
    08-27-2019
    Location
    Alabama, USA
    MS-Off Ver
    365, 2016, 2010, 2007
    Posts
    57

    Re: Copy sheet into 3D array

    I was trying to modify it to only take the sheets that end with a particular suffex, but got stuck again. I don't think it is seeing my wildcard correctly.

    This is what I currently have, any idea where I am messing up?
    Or if there's a different or better way to make it run only on sheets that end with _RAW (or _COR sometimes, or _IDEAL sometimes, or _ZERO sometimes).

    Please Login or Register  to view this content.

  20. #20
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,619

    Re: Copy sheet into 3D array

    You can't use wildcards like that in a Select Case statement, why not use a simple if statement?
    Please Login or Register  to view this content.

  21. #21
    Registered User
    Join Date
    08-27-2019
    Location
    Alabama, USA
    MS-Off Ver
    365, 2016, 2010, 2007
    Posts
    57

    Re: Copy sheet into 3D array

    Okay cool, I think I have exactly what I need now! Thank you all your help and for putting up with me as I learn how to work with VBA.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA copy specified columns from one sheet to another using array
    By Tunesmith in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-31-2019, 11:59 AM
  2. [SOLVED] Search Array Terms Copy Row Other Sheet
    By Logit in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-13-2018, 03:47 PM
  3. copy from sheet to array
    By twozedz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-14-2016, 06:57 AM
  4. How to copy an array to excel sheet?
    By helpmeimlost in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-07-2014, 02:24 AM
  5. Replies: 0
    Last Post: 06-28-2014, 02:26 PM
  6. Copy value to Array variables use them in another sheet!
    By ravikiran in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-19-2012, 04:15 AM
  7. Copy from one sheet using an array
    By peakymatt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-12-2009, 09:23 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1