I need help with this

[smittycft99]

Trying to sum a column of data and fill the next cell below with this value. But I each column length is unknown beforehand.

Is this close?
============================

Dim LastR As Integer, CurrRow As Integer
Dim PercRange As Range, Thing
Sheets("Sheet1").Activate 'Activate sheet
Range("A1").Select 'Select top of column
Selection.End(xlDown).Select 'Find bottom of column
LastR = ActiveCell.Row 'Get row number
Range(Cells(2, 1), Cells(LastR, 1)).Select

'sum entries in column B that are >10
' =SUM(IF(B10:B50>10,B10:B50,0))
'A = Application.WorksheetFunction.sum(a1, a531)
'a = Application.WorksheetFunction.sum(Cells(a, 1), Cells(a, 531))

'Range("A1:A7").Select
'Range("A7").Activate
'ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"
'Range("C12").Select
'ActiveCell.FormulaR1C1 = "=SUM(R[-11]C[-2]:R[-6]C[-2])"

'Range("A1:A7").Select
Range("A532").Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-531]C:R[-1]C)"
Range("A532").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-531]C:R[-1]C)"

[smittycft99]

Al or Mark W. any ideas

[Jay Petrulis]

Hi,

I'm not Al or Mark W, but try:

'--------
Sub sum_col_a_data()
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

' this loads the SUM function
Cells(lastrow + 1, 1) = "=SUM(R[-" & lastrow & "]C:R[-1]C)"

' this loads the SUMIF function
Cells(lastrow + 1, 1) = "=SUMIF(R[-" & lastrow & "]C:R[-1]C,"">10"")"

End Sub
'------------

I don't understand which of these you need from your code.

HTH,
Jay

[smittycft99]

Jay,

This works perfect, but all of the summations are going to the end of A column. If I'm summing up column B, I need the sum to be at the end of column B. I put the code into the macro and assigned it a shortcut key. I went to the header of each column and ran it and the summation for each column goes at the bottom of A....

[Jay Petrulis]

Hi,

This will do what you want. You need to decide which formula (or write another) you want loaded. This one actually loads them both, but the SUMIF overwrites the SUM function.

'-----------
Sub sum_col_data()
Dim lastrow As Long, x As Long
Dim ColCount As Integer

With ActiveSheet
.UsedRange
ColCount = .Cells.SpecialCells(xlCellTypeLastCell).Column
End With

For x = 1 To ColCount
lastrow = Cells(Rows.Count, x).End(xlUp).Row
Cells(lastrow + 1, x) = "=SUM(R[-" & lastrow & "]C:R[-1]C)"
Cells(lastrow + 1, x) = "=SUMIF(R[-" & lastrow & "]C:R[-1]C,"">10"")"
Next x
End Sub
'--------------

Bye,
Jay

[smittycft99]

Thanks, Jay you're a lifesaver. I really appreciate your help