I need help with this

smittycft99

Board Regular
Joined
Apr 16, 2002
Messages
128
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)"
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0
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....
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top