Hi

Thanks for your reply.

Your debug statement works fine...

Sum are calculated and pasted to sheet L1


Option Explicit
Option Base 1

Sub SumArray()
Dim TopCol As Long 'Rightmost Column #
Dim Col As String ' Rightmost Column Letter
Dim HeadersRng As Range 'Headers Row
Dim SourceArray() As Variant ' Array to receive Data Source Range
Dim SumArray As Variant ' Array summarizing SourceArray
Dim BotRow As Long
Dim SumCol As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim r As Long
Dim c As Long
Dim aVal As String
Dim bVal As String
'Find Column in Row 1 containing word "Total Cost"
Worksheets("L2").Activate
Set HeadersRng = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
TopCol = HeadersRng.Find(What:="Total Cost", LookAt:=xlWhole).Column - 1
BotRow = (Cells(65536, 1).End(xlUp).Row)
Col = Left(Cells(1, TopCol).Address(0, 0), 1 - (TopCol 26))
SumCol = TopCol + 1

With Sheets("L2")
SourceArray = Range("A2:" & Col & BotRow).Value
End With

' Add a watch from the Debug menu and inset a break point to stop and
'check the values in the array in the Watches window


'Initialize SumArray Column "A". Number of Summary Rows is limited to 3

ReDim SumArray(4, SumCol)
SumArray(1, 1) = "A"
SumArray(2, 1) = "B"
SumArray(3, 1) = "C"
SumArray(4, 1) = "Sum"

' Summarize SourceArray into SumArray
For i = LBound(SumArray) To UBound(SumArray)
For j = 1 To BotRow - 1
aVal = SumArray(i, 1)
bVal = SourceArray(j, 1)
If aVal = bVal Then
For k = 2 To TopCol
SumArray(i, k) = SumArray(i, k) + SourceArray(j, k)
'row sum
SumArray(4, k) = SumArray(4, k) + SourceArray(j, k)
'Column sum
Select Case aVal
Case Is = "A"
SumArray(1, SumCol) = SumArray(1, SumCol) +
SourceArray(j, k)
Case Is = "B"
SumArray(2, SumCol) = SumArray(1, SumCol) +
SourceArray(j, k)
Case Is = "C"
SumArray(3, SumCol) = SumArray(1, SumCol) +
SourceArray(j, k)
End Select
Next
End If
Next
Next

'Write SumArray back in Sheets("L1") starting in Cell A10
For r = LBound(SumArray) To UBound(SumArray)
For c = LBound(SumArray, 2) To UBound(SumArray, 2)
Sheets("L1").Range("A10").Offset(r - 1, c - 1).Value = SumArray(r,
c)
Next
Next
Debug.Print LBound(SumArray, 1), UBound(SumArray, 1), LBound(SumArray, 2),
UBound(SumArray, 2)
End Sub

Regards,
Per
"u473" skrev i meddelelsen
...
I did appreciate reading your solution like a brilliant move in Chess.
Now, I could come up with some convoluted and unesthetic SUM code
to have a Row and Columns Totals for r and c.
I could do it from the range, but what is the expert way of doing it
from the Array ?
In addition, for testing purpose I was trying to use Debug.print to
check the wole array after the last next.
But I get weird results in the immediate window.
This is the Debug.Print statement I used, and the syntax may in
error..
Debug.Print LBound(SumArray, 1), UBound(SumArray, 1), LBound(SumArray,
2), UBound(SumArray, 2)
Thank you again,
J.P.