I have some code that will filter a table in excel on two factors, sum the
visible rows in a particular column, store the sums in a 2 dimensional array
and transpose the array to another workbook. I believe the array is being
created properly; however, it does not transpose correct. Here is the code.
Any help would be appreciated.
Dim iCalWeek As Integer ' Variable for calendar week number
Dim iRow As Integer ' Row variable for array
Dim iCol As Integer ' Column variable for array
Dim wsTop8DC As Worksheet ' Variable for Top 8 Worksheet
Dim tblTop8DC As ListObject ' Variable for Top 8 Defect Count
' 2 dim array to hold Loop 4 count on 8 issues for past 7 calendar weeks
Dim iLoop4Totals(1 To 7, 1 To 8) As Integer
' Set iCalWeek to 8 weeks before current week
iCalWeek = WorksheetFunction.WeekNum(Date) - 8
Set wsTop8DC = Workbooks("Tables").Sheets("Top8DefectCount")
Set tblTop8DC = wsTop8DC.ListObjects("tblTop8DC")
For iRow = 1 To 7
With tblTop8DC
.Range.AutoFilter Field:=7 ' Clear Filter
' Filter table based on calendar week
.Range.AutoFilter Field:=7, Criteria1:=(iCalWeek + iRow)
End With
For iCol = 1 To 8
With tblTop8DC
.Range.AutoFilter Field:=3 ' Clear Filter
' Filter table based on issue number
.Range.AutoFilter Field:=3, Criteria1:=iCol
End With
With wsTop8DC
' Set array value to sum of issues occurance
iLoop4Totals(iRow, iCol) = _
WorksheetFunction.Sum(.Range("F:F").SpecialCells(xlCellTypeVisible))
End With
Next
Next
' Transpose Array
With Workbooks("Top8Slides").Sheets("Data")
.Range("B4:I10") = WorksheetFunction.Transpose(iLoop4Totals)
End With
|
Similar Threads
