Prev: Accounting question
Next: Pivot Table/Conditional Formatting Guru Help
From: Michele on 12 May 2010 13:37
I'm trying to create a macro and I'm having trouble with the last part. I
want to total a column (E) but the number of rows will vary day to day. For
example this totaled today's file with 726 rows but tomorrow's file might
have 800 or 500 rows. How do I account for the various row size?

Range("E13").Select
Selection.End(xlDown).Select
Range("E726").Select
Selection.FormulaR1C1 = "=SUM(R[-724]C:R[-1]C)"

From: PY & Associates on 14 May 2010 01:18
On May 13, 1:37 am, Michele <Mich...(a)discussions.microsoft.com> wrote:
> I’m trying to create a macro and I’m having trouble with the last part.  I
> want to total a column (E) but the number of rows will vary day to day.  For
> example this totaled today’s file with 726 rows but tomorrow’s file might
> have 800 or 500 rows.  How do I account for the various row size?
>
>     Range("E13").Select
>     Selection.End(xlDown).Select
>     Range("E726").Select
>     Selection.FormulaR1C1 = "=SUM(R[-724]C:R[-1]C)"

something like this

Sub n()
Range("E13").Select
Selection.End(xlDown).Select

Dim frow As Integer
Dim lrow As Integer
frow = Selection.Cells(1, 1).Row 'you have made it as 13
lrow = Selection.Rows.Count + frow - 1

Dim n As Integer
n = lrow - frow + 2
Cells(lrow + 2, "E").FormulaR1C1 = "=SUM(R[-" & n & "]C:R[-2]C)"
End Sub
 | 
Pages: 1
Prev: Accounting question
Next: Pivot Table/Conditional Formatting Guru Help