# Thread: 3D Array (Excel 2003)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•