FormulaR1C1 To Sum data

  • Thread starter Thread starter barryjonunattended
  • Start date Start date
B

barryjonunattended

Hi,

Please forgive me - I am not great when it comes to Excel VBA. I
believe there is an easy way to sum a column in VBA using the
forumalaR1C1= syntax.

So say I have a report which regularly has a different number of rows.
I want to sum on columns C, D, E, F etc...(all the way to column AT)
where the starting row to be summed is 7 (and obviously the ending row
is something like lngLastRow). I want to insert the "Grand Total" row
on lngLastRow + 1. I know I can explicitly do something like this for
each row;

Cells(lngLastRow+1, 3).Value = "=sum(C7:C" & lngLastRow & ")"

The problem with this is I need to explicitly write a line of code for
each line. Is there a way using the R1C1 method to right a more
generic line and look through the columns? Or am I just completely way
off the mark. If there is a way could you explain how it works or
point me to a good link where it is explained (simply and clearly).

Any help much appreciated.

Barry-Jon
 
Oops - that's;

The problem with this is that I need to explicitly write a line of code
for
each COLUMN to be summed. Is there a way using the R1C1 method to
WRITE a more generic line and LOOP through the columns (by number)? Or
am I just completely way off the mark. If there is a way could you
explain how it works or
point me to a good link where it is explained (simply and clearly).

(See what happens when you let your fingers do the talking...)
 
Barry-Jon,

You don 't need R1C1 to make it generic

For i = 3 to 46
sCol = ColumnLetter(i)
Cells(lngLastRow+1, 3).Formula= "=SUM(" & scol & "7:" sCol &
lngLastRow & ")"
Next i

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
sColumn = Split(Columns(Col).Address(, False), ":")(1)
ColumnLetter = sColumn
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top