Arun,

Thanks for replyihg back. I think I figured this out.

I'm creating a VB module to value non-performing loans, and I want to add summary stats on the portfolio and the expected performance, generated automatically via VBA in Excel.

So, I want to loop over the columns, adding summaries for each columns, such as:

=sum(The entries in the columns), or

=Sumproduct(Column(0), Column(j))/Sum(Column(0))

etc.

Now I understand that inside of VBA this can be done by string processing in VB to generate the right string in the Worksheet cell:

Here is an example: (I would think this can be simplified....)

Sub Summary()

Dim rng As Range, rng0 As Range
Dim cNumRow As Integer, cNumCol As Integer, j As Integer

cNumRow = Range([A2], [A2].End(xlDown)).count

cNumCol = Range([A2], [A2].End(xlToRight)).count

Set rng0 = Range([A2], [A2].Offset(cNumRow - 1))


For j = 1 To 4
Set rng = Range(Cells(2, j), Cells(cNumRow + 1, j))
With [A1].Offset(0, j - 1)
If (j = 1) Then
.Formula = "=sum(" & rng.Address(0, 0) & ")"
ElseIf (j = 2) Then
.Formula = "=average(" & rng.Address(0, 0) & ")"
ElseIf (j = 3) Then
.Formula = "=sumproduct(" & rng0.Address(0, 0) & "," & rng.Address(0, 0) & ")"
ElseIf (j = 4) Then
.Formula = "=sumproduct(" & rng0.Address(0, 0) & "," & rng.Address(0, 0) & ")/sum(" & rng0.Address(0, 0) & ")"
End If
End With
Next j

End Sub