Array or Pivot Tables???

[Lonnie M.]

Array or Pivot Tables???

Hi, I am trying to find a more efficient means of getting my period
data.
Below is an example of code that I use to get the current period's
performance data for a given activity by taking the difference from the
current period's cumulative data and the previous period's
cumulative data. Each contract block has 4 to 8 hulls. Each contract
block's cumulative data for a given period has approximately +50K
rows of data. Pivot tables are quick but I have to break the hulls up
so that I can fit the current and previous data on one sheet. If I use
an array or index/match functions it takes about +20 minutes to
generate the period data.

Is there a better way to use an array that is capable of approaching
the speeds at which a pivot table is able to perform this task?

wbC -- worksheet where the current cum data is
wbP -- worksheet where the previous cum data is
wb3 -- worksheet where the matched period data is placed


'######################################################
'LOAD CURRENT ARRAY
cntCur = wbC.Cells(Rows.Count, "A").End(xlUp).Row
arrCur = wbC.Range("A1:M" & cntCur)
'LOAD PREVIOUS ARRAY
cntPrev = wbP.Cells(Rows.Count, "A").End(xlUp).Row
arrPrev = wbP.Range("A1:M" & cntPrev)
For C = 1 To cntCur
Call ShowUserForm 'Progress Bar
wb3.Cells(C, 1) = arrCur(C, 1) 'Corp
wb3.Cells(C, 2) = arrCur(C, 2) 'Hull
wb3.Cells(C, 3) = arrCur(C, 3) 'MM
wb3.Cells(C, 4) = arrCur(C, 4) 'KE
wb3.Cells(C, 5) = arrCur(C, 5) 'LT
wb3.Cells(C, 6) = arrCur(C, 6) 'HT
wb3.Cells(C, 7) = arrCur(C, 7) 'ACTIVITY
wb3.Cells(C, 8) = arrCur(C, 8) 'DESC
wb3.Cells(C, 9) = arrCur(C, 9) 'CURRENT TAC
wb3.Cells(C, 10) = arrCur(C, 10) 'CURRENT TCWS
wb3.Cells(C, 11) = arrCur(C, 11) 'CURRENT TCWP
wb3.Cells(C, 12) = arrCur(C, 12) 'CURRENT ACWP
For P = 1 To cntPrev
'Match Current Acitvity with Previous Acitvity
If arrCur(C, 1) & arrCur(C, 2) & arrCur(C, 3) & arrCur(C, 4) = _
arrPrev(P, 1) & arrPrev(P, 2) & arrPrev(P, 3) & arrPrev(P, 4) Then
If C = 1 Then
'1st pass skip & insert headers for period data
GoTo MoveOn1
Else
wb3.Cells(C, 13) = arrCur(C, 9) - arrPrev(P, 9) 'per TAC
wb3.Cells(C, 14) = arrCur(C, 10) - arrPrev(P, 10) 'per TCWS
wb3.Cells(C, 15) = arrCur(C, 11) - arrPrev(P, 11) 'per TCWP
wb3.Cells(C, 16) = arrCur(C, 12) - arrPrev(P, 12) 'per ACWP
wb3.Cells(C, 17) = _
(arrCur(C, 11) - arrPrev(P, 11)) - (arrCur(C, 12) - arrPrev(P, 12))
'per CV
wb3.Cells(C, 18) = _
(arrCur(C, 11) - arrPrev(P, 11)) - (arrCur(C, 10) - arrPrev(P, 10))
'per SV
End If
'Match found step out of Previous Loop
GoTo MoveOn1
End If
Next P
MoveOn1:
If C = 1 Then
'Insert period headers on 1st pass
wb3.Cells(C, 13) = "perTAC"
wb3.Cells(C, 14) = "perTCWS"
wb3.Cells(C, 15) = "perTCWP"
wb3.Cells(C, 16) = "perACWP"
wb3.Cells(C, 17) = "perCV"
wb3.Cells(C, 18) = "perSV"
ElseIf P > cntPrev Then 'No activity matched
wb3.Activate
wb3.Range(Cells(C, 13), Cells(C, 18)).Select
For Each cell In Selection.Cells
cell.Value = 0
Next
End If
Application.StatusBar = _
"Row " & Format(C, "#,###") & " of " & Format(cntCur, "#,###")
Next C
Thanks in advance, Lonnie M.
P.S. Harlan and Alan, play nice now

 

[Lonnie M.]

Correction, the previous loop's 'If' statement should be:
For P = 1 To cntPrev
'Match Current Acitvity with Previous Acitvity
If arrCur(C, 4) & arrCur(C, 5) & arrCur(C, 6) & arrCur(C, 7) = _
arrPrev(P, 4) & arrPrev(P, 5) & arrPrev(P, 6) & arrPrev(P, 7) Then