Results 1 to 7 of 7
Thread: 3D Array (Excel 2003)

20060130, 15:51 #1
 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.

20060130, 16:19 #2
 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 2D 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

20060130, 17:11 #3
 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.

20060130, 17:52 #4
 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

20060131, 11:20 #5
 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.

20060131, 11:40 #6
 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

20060131, 15:45 #7
 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.