+ Reply to Thread
Results 1 to 2 of 2

Array or Pivot Tables???

  1. #1
    Lonnie M.
    Guest

    Array or Pivot Tables???

    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 *** data is
    wbP -- worksheet where the previous *** 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


  2. #2
    Lonnie M.
    Guest

    Re: Array or Pivot Tables???

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1