Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    3D Array (Excel 2003)

    Hi all,

    I would like to ask can this be done?

    I have 3 columns of data, the first column is a date, the second column is an integer, and the third is an integer. What I use this sheet for is, I find a date, and take the offset value of the next 2 columns, the values in columns B is 1 to 9, and column C is 1 to 8 repeated so that at some point in the future I can access a week and determine what the work week is for that particular week, and know what the schedule is for each individual staff member.

    What I have now works but, can I make a 3 dimensional array so that if I select a date on a calendar it does a count and loops thru the date until that day and the value of the second and third array count along with it?

    So that if Jan 1 06 is my selection, 9 and 1 would be the resulting values for my work week.

    If someone could get me started I would much appreciated it.

    Thanks Darryl.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 3D Array (Excel 2003)

    I don't understand what you need a 3D Array for? It seems the 2-D array works fine. It can work with OFFSET, VLOOKUP, or MATCH /INDEX to read the selection, find it in A and return the item from B/C.

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>If You go the 3D Array route, where do you want to store it (in VB?) and what value do you want to place in each of the "cells" of the array? You will be creating an array and presumably given the 3 "coordinates" (items from A, B, C) you will get the 4th from the "intersection". This does not seem to be what you are asking about.

    If you are just looking for workweeks from a date, you should be able to calculate this without any table. What do the 2 integer numbers mean from the table?

    Steve

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: 3D Array (Excel 2003)

    Steve,

    The 2 integers are the numbers that are used for scheduling staff to work, one group of staff are on one schedule and the other are on the next offset value, So that if group B is in week 9(cell value), and group C is on week 2(cell value), I pick up those values and based on those values I know what the schedule is, use that # to run a select case then use that value to perform a procedure based on those 2 numbers.

    What I have now I posted in the example, that's how I grab those values with a cells.find, then find the offsetting value, I guess what I would like to know is how would I accomplish finding those two values programmatically.

    i'm guessing here when I ask, should I use a For next loop based on the count of the calendar, and use a counter to count an integer from 1 to 9 and 1 to 8 based on what I have on the worksheet.

    Thanks Darryl.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 3D Array (Excel 2003)

    If the date "selected" is in cell E1, you can use the formulas:

    <pre>=VLOOKUP(E1,$A$1:$C$55,2,0)

    =VLOOKUP(E1,$A$1:$C$55,3,0)</pre>


    to get the 2 values.

    if you want to use a macro, you could have the formulas in the sheet and read the values or directly:
    <pre>Option Explicit
    Sub Example()
    Dim x As Long
    Dim iVal1 As Integer
    Dim iVal2 As Integer
    Dim rng As Range
    Dim AWF As WorksheetFunction
    Dim rDate As Range

    Set rDate = Worksheets("Sheet1").Range("E1")
    Set rng = Worksheets("Sheet1").Range("A1:C55")
    Set AWF = Application.WorksheetFunction
    x = 0
    On Error Resume Next
    x = AWF.Match(rDate.Value2, Intersect(rng, rng.Columns(1)), 0)
    On Error GoTo 0
    If x = 0 Then
    MsgBox "That date is not in the list"
    Else
    iVal1 = rng.Cells(x, 2)
    iVal2 = rng.Cells(x, 3)
    MsgBox "Col 1 = " & iVal1 & vbCrLf & _
    "Col 2 = " & iVal2
    End If
    Set rDate = Nothing
    Set rng = Nothing
    Set AWF = Nothing
    End Sub</pre>


    Steve

  5. #5
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: 3D Array (Excel 2003)

    Thanks Steve,

    This is a question that I have to solve, for my own resolution - Is what I have in my example an array? a 3D array?

    How can I count the number of days between the date selected, and a date that would be constant like Janary 1 2006 ,but do it in code.

    Thanks Darryl.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 3D Array (Excel 2003)

    Your example as listed is a 2D array. You have rows and columns. Given a row and a column (2 items) you get the third (the intersection). If you have a 3D array, you would be given 3 values to obtain the fourth.

    You can subtract dates directly to get the number of days between them. Excel stores dates in units of days.

    Steve

  7. #7
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: 3D Array (Excel 2003)

    Thanks Steve,
    I just wanted to post what I figured out, actually it was a lot easier than I had thought.

    Public Const xDate As Date = "12 / 31 / 05"
    Private Sub CommandButton1_Click()
    Dim iWeek As Integer
    Dim Isweek As Integer
    Dim z As Integer
    z = Int((DTPicker1.Value - xDate) / 7)
    iWeek = 1
    Isweek = 1
    For i = i + 1 To z
    iWeek = iWeek + 1
    Isweek = Isweek + 1
    If iWeek = 14 Then
    iWeek = 1
    End If
    If Isweek = 3 Then
    Isweek = 1
    End If
    Next i
    Me.Label1.Caption = iWeek
    Me.Label2.Caption = Isweek

    End Sub


    Darryl.

Posting Permissions

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