Adding Values Within A Multi-Dimensional Array

[mmcglad1]

I'm stumped and will try to explain this as best as I can. I'm using one sheet as a database and the other for generating a report. When the user enters a begin date (20130801) and end date (20130808), the Report workbook will call upon the Database workbook. Column "A" of the Database workbook is used for labeling and is setup like this:

<code>Date
Dept1
060000
...... 'Every 15 min from 6am to 7pm (53 15min time intervals)
190000
Dept2
060000
...... 'Every 15 min from 6am to 7pm (53 15min time intervals)
190000
Dept3 ... 'For a total of 5 dept.

</code>I can use .Find to find the first column based on the user's requested begin date and then a Do While loop to cycle through each column until Date (Row 1, Column ?) = end date, but I need help finding a way to add the data under each column to an array and then preserve and add additional data to the array when cycling through the Do While loop. When done with the Do While loop, I'll take the total values and paste them in a report. Here is an example:

<code>Date.....20130801...20130802...And So On
Dept1...."Blank"...."Blank"...
060000...5..........1.........
061500...6..........2.........
063000...7..........3......... </code>...

Using Dept1(060000) as an example, I need the array to add each value for each day at this time until the end date is reached. I'll later need to extract that sum, divide by the number of days, and add the value to a cell on the report. Any help would be extremely appreciated.

 

[mmcglad1]

Figured it out:

Sub WorkArrivalHistoryGraph()
'
' WorkArrivalHistoryGraphRetrData
'

Dim colBegin As Variant
Dim colEnd As Variant
Dim r As Variant
Dim x As Variant
Dim index As Integer
Dim BeginDate As Variant
Dim EndDate As Variant
Dim ColCount As Integer
Dim wsReport As Worksheet
Dim wsData As Worksheet
Dim wkbkReport As Workbook
Dim wkbkData As Workbook
Dim FindColumn1 As Range
Dim FindColumn2 As Range

Dim ar1(52) As Double
Dim ar2(52) As Variant
Dim ar3(52) As Variant
Dim ar4(52) As Variant
Dim ar5(52) As Variant
Dim ar6(52) As Variant

Set wkbkReport = Workbooks("?.xlsm")
Set wkbkData = Workbooks("?.xlsm")

Set wsReport = wkbkReport.Worksheets("WAHistory")
Set wsData = wkbkData.Worksheets("WorkArrival")

BeginDate = wsReport.Range("B1").Value
EndDate = wsReport.Range("B2").Value

With wkbkData.Sheets("WorkArrival")
Set FindColumn1 = wsData.UsedRange.Find(What:=BeginDate, LookIn:=xlValues)
End With

colBegin = FindColumn1.Column 'this is the first column where you want to check for data
With wkbkData.Sheets("WorkArrival")
Set FindColumn2 = wsData.UsedRange.Find(What:=EndDate, LookIn:=xlValues)
End With

colEnd = FindColumn2.Column
x = colBegin
ColCount = 0
Do While (wsData.Cells(1, x).Value >= BeginDate And wsData.Cells(1, x).Value <= EndDate)
'This will loop until row 1 is empty
x = x + 1
ColCount = ColCount + 1
Loop
wkbkData.Activate
Sheets("WorkArrival").Select

'Retrieve Data
index = 0
For r = 3 To 55
ar1(index) = WorksheetFunction.Sum(wsData.Range(Cells(r, colBegin), Cells(r, colEnd)))
index = index + 1
Next
index = 0
For r = 57 To 109
ar2(index) = WorksheetFunction.Sum(wsData.Range(Cells(r, colBegin), Cells(r, colEnd)))
index = index + 1
Next
index = 0
For r = 111 To 163
ar3(index) = WorksheetFunction.Sum(wsData.Range(Cells(r, colBegin), Cells(r, colEnd)))
index = index + 1
Next
index = 0
For r = 165 To 217
ar4(index) = WorksheetFunction.Sum(wsData.Range(Cells(r, colBegin), Cells(r, colEnd)))
index = index + 1
Next
index = 0
For r = 219 To 271
ar5(index) = WorksheetFunction.Sum(wsData.Range(Cells(r, colBegin), Cells(r, colEnd)))
index = index + 1
Next
index = 0
For r = 273 To 325
ar6(index) = WorksheetFunction.Sum(wsData.Range(Cells(r, colBegin), Cells(r, colEnd)))
index = index + 1
Next
'Update Report
wkbkReport.Activate
Sheets("WAHistory").Select
index = 0
For r = 4 To 56
Range("C" & r) = ar1(index)
Range("D" & r) = ar2(index)
Range("E" & r) = ar3(index)
Range("F" & r) = ar4(index)
Range("G" & r) = ar5(index)
Range("H" & r) = ar6(index)
index = index + 1
Next
End Sub