+ Reply to Thread
Results 1 to 3 of 3

How do you create three dimensional arrays and assign their values from mult worksheets?

  1. #1
    Registered User
    Join Date
    08-18-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    How do you create three dimensional arrays and assign their values from mult worksheets?

    Hi all,

    I'm a beginner in excel VBA and I would like to know if you can help me start creating my macro. Here's the context of the problem:

    Assume an automotive manufacturing plant that has 4 different lines to create 4 different vehicles. Each line has their own key performance indicators. Each worksheet represents different line by week. The desired output would be graphs showing the performance of each line by week. What I imagine is a dynamic graph with a drop-down list to change the line, where y-axis is the OEE and the x-axis is weekly OEE.

    I assume that this would require a three dimensional array (Line x, performance indicator y, week z) and some of these inputs would come from the worksheet name that might or might not be standardize. I attach the sample of the input and I would really appreciate if you have any input.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,964

    Re: How do you create three dimensional arrays and assign their values from mult workshee

    Don't use merged cells, you get in trouble with it sooner or later.

    Maybe like this.

    2 macro's

    After that I made an table of the data.

    After that I made an pivot table.

    After that I made 2 graphs.

    Please reply.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    08-18-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: How do you create three dimensional arrays and assign their values from mult workshee

    Hi,

    Thank you for your reply. I find the approach very interesting. But since I'm a beginner, I'm still having trouble understanding the 2 macros you have.

    However, while waiting for replies, I also have made a wee bit of progress on my own and I successfully store values into arrays and copy that into my "Overview" tab.

    From here, I would like to create one line chart which the user can change using a combobox (i.e. Line 1, Line 2, etc).

    Note: The number of week and line do vary depending on the input. Thus, the table on the overview tab might also change in size.

    Thank you!

    Quote Originally Posted by oeldere View Post
    Don't use merged cells, you get in trouble with it sooner or later.

    Maybe like this.

    2 macro's

    After that I made an table of the data.

    After that I made an pivot table.

    After that I made 2 graphs.

    Please reply.
    Attached Files Attached Files

+ 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. Two-dimensional arrays
    By abousetta in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 06-10-2011, 01:40 AM
  2. Passing Multi-Dimensional Arrays
    By dldecler in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-28-2011, 02:03 PM
  3. Multi-Dimensional Arrays
    By ajocius in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-09-2009, 11:20 AM
  4. bi-dimensional arrays
    By ina in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-28-2006, 07:25 AM
  5. Multi-dimensional arrays
    By gti_jobert in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-06-2006, 12:45 AM

Tags for this Thread

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