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(x lCellTypeVisible))
End With
Next
Next
' Transpose Array
With Workbooks("Top8Slides").Sheets("Data")
.Range("B4:I10") = WorksheetFunction.Transpose(iLoop4Totals)
End With

May be
..range("B4:H11") = WorksheetFunction.Transpose(iLoop4Totals)

"Brad" wrote in message
...
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(x lCellTypeVisible))
End With
Next
Next
' Transpose Array
With Workbooks("Top8Slides").Sheets("Data")
.Range("B4:I10") = WorksheetFunction.Transpose(iLoop4Totals)
End With

Dear Brad

I did not test this but after transposing the range should be B4:H11 instead
of B4:I10. Please try and feedback.

..Range("B4:H11") = WorksheetFunction.Transpose(iLoop4Totals)

If this post helps click Yes
--------------
Jacob Skaria

"Brad" wrote:

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(x lCellTypeVisible))
End With
Next
Next
' Transpose Array
With Workbooks("Top8Slides").Sheets("Data")
.Range("B4:I10") = WorksheetFunction.Transpose(iLoop4Totals)
End With

Actually it was
..Range("B4:H10) = WorksheetFunction.Transpose(iLoop4Totals)

With H11 I got an extra row of 0's.

Thanks so much for the help. Couldn't have figured it out without it.

"Jacob Skaria" wrote:

Dear Brad

I did not test this but after transposing the range should be B4:H11 instead
of B4:I10. Please try and feedback.

.Range("B4:H11") = WorksheetFunction.Transpose(iLoop4Totals)

If this post helps click Yes
--------------
Jacob Skaria

"Brad" wrote:

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(x lCellTypeVisible))
End With
Next
Next
' Transpose Array
With Workbooks("Top8Slides").Sheets("Data")
.Range("B4:I10") = WorksheetFunction.Transpose(iLoop4Totals)
End With