ExcelBanter - vba, excel, sum different ranges

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - vba, excel, sum different ranges (https://www.excelbanter.com/excel-worksheet-functions/39506-vba-excel-sum-different-ranges.html)

vba, excel, sum different ranges

Hi,

Before I posted this question, I researched on similar questions on this NG
and I didn't get the answer I needed. All I need is a formula that would sum
numbers above the active cell.

This is the formula I have.

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

It works but it is only good if there are four or less numbers above it.

What I need is a formula for variable numbers to add.

Thank you.

How about

ActiveCell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"

--
HTH

Bob Phillips

"Danny" wrote in message
...
Hi,

Before I posted this question, I researched on similar questions on this
NG
and I didn't get the answer I needed. All I need is a formula that would
sum
numbers above the active cell.

This is the formula I have.

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

It works but it is only good if there are four or less numbers above it.

What I need is a formula for variable numbers to add.

Thank you.

Danny,

Sub SumAtBottomOfCurrentColumn()
Dim myCell As Range
Set myCell = Cells(65536, ActiveCell.Column).End(xlUp)(2)
'Uncomment this section to sum from a consistent row: in this example, row 2
'And comment out the lower section of code
'With myCell
' .Formula = "=SUM(" & _
' Range(.Offset(-1, 0), _
' Cells(2, .Column)).Address(False, False) & ")"
'End With

'Use this to sum the block up from the lowest cell
With myCell
.Formula = "=SUM(" & _
Range(.Offset(-1, 0), _
.Offset(-1, 0).End(xlUp)).Address(False, False) & ")"
End With
End Sub

HTH,
Bernie
MS Excel MVP

"Danny" wrote in message
...
Hi,

Before I posted this question, I researched on similar questions on this NG
and I didn't get the answer I needed. All I need is a formula that would sum
numbers above the active cell.

This is the formula I have.

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

It works but it is only good if there are four or less numbers above it.

What I need is a formula for variable numbers to add.

Thank you.

Thank you for the response. Can you please modify to add only to up to last
cell?
Let's say the active cell is A15 and the numbers above it is from A10 to
A14, the formula would only add A10 (A9 is blank) to A14.

Then if my macro would select A30 as the active cell and there are numbers
from A21 (A20 is blank)to A29, it would only add A21 to A29.

The formula you gave adds from A1.

Thanks again.

"Bob Phillips" wrote:

How about

ActiveCell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"

--
HTH

Bob Phillips

"Danny" wrote in message
...
Hi,

Before I posted this question, I researched on similar questions on this
NG
and I didn't get the answer I needed. All I need is a formula that would
sum
numbers above the active cell.

This is the formula I have.

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

It works but it is only good if there are four or less numbers above it.

What I need is a formula for variable numbers to add.

Thank you.

Hi Bernie,

I tried the formula and sometimes it works. It also jumps one cell below. It
there a simple way to modify the original formula to make "-4" variable?

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

"Bernie Deitrick" wrote:

Danny,

Sub SumAtBottomOfCurrentColumn()
Dim myCell As Range
Set myCell = Cells(65536, ActiveCell.Column).End(xlUp)(2)
'Uncomment this section to sum from a consistent row: in this example, row 2
'And comment out the lower section of code
'With myCell
' .Formula = "=SUM(" & _
' Range(.Offset(-1, 0), _
' Cells(2, .Column)).Address(False, False) & ")"
'End With

'Use this to sum the block up from the lowest cell
With myCell
.Formula = "=SUM(" & _
Range(.Offset(-1, 0), _
.Offset(-1, 0).End(xlUp)).Address(False, False) & ")"
End With
End Sub

HTH,
Bernie
MS Excel MVP

"Danny" wrote in message
...
Hi,

Before I posted this question, I researched on similar questions on this NG
and I didn't get the answer I needed. All I need is a formula that would sum
numbers above the active cell.

This is the formula I have.

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

It works but it is only good if there are four or less numbers above it.

What I need is a formula for variable numbers to add.

Thank you.

Danny,

Try this version.

Sub SumInActiveCell()
With ActiveCell
.Formula = "=SUM(" & _
Range(.Offset(-1, 0), _
.Offset(-1, 0).End(xlUp)).Address(False, False) & ")"
End With
End Sub

HTH,
Bernie
MS Excel MVP

"Danny" wrote in message
...
Hi Bernie,

I tried the formula and sometimes it works. It also jumps one cell below. It
there a simple way to modify the original formula to make "-4" variable?

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

"Bernie Deitrick" wrote:

Danny,

Sub SumAtBottomOfCurrentColumn()
Dim myCell As Range
Set myCell = Cells(65536, ActiveCell.Column).End(xlUp)(2)
'Uncomment this section to sum from a consistent row: in this example, row 2
'And comment out the lower section of code
'With myCell
' .Formula = "=SUM(" & _
' Range(.Offset(-1, 0), _
' Cells(2, .Column)).Address(False, False) & ")"
'End With

'Use this to sum the block up from the lowest cell
With myCell
.Formula = "=SUM(" & _
Range(.Offset(-1, 0), _
.Offset(-1, 0).End(xlUp)).Address(False, False) & ")"
End With
End Sub

HTH,
Bernie
MS Excel MVP

"Danny" wrote in message
...
Hi,

Before I posted this question, I researched on similar questions on this NG
and I didn't get the answer I needed. All I need is a formula that would sum
numbers above the active cell.

This is the formula I have.

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

It works but it is only good if there are four or less numbers above it.

What I need is a formula for variable numbers to add.

Thank you.

Bernie,

Perfect! Thanks a lot!

"Bernie Deitrick" wrote:

Danny,

Try this version.

Sub SumInActiveCell()
With ActiveCell
.Formula = "=SUM(" & _
Range(.Offset(-1, 0), _
.Offset(-1, 0).End(xlUp)).Address(False, False) & ")"
End With
End Sub

HTH,
Bernie
MS Excel MVP

"Danny" wrote in message
...
Hi Bernie,

I tried the formula and sometimes it works. It also jumps one cell below. It
there a simple way to modify the original formula to make "-4" variable?

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

"Bernie Deitrick" wrote:

Danny,

Sub SumAtBottomOfCurrentColumn()
Dim myCell As Range
Set myCell = Cells(65536, ActiveCell.Column).End(xlUp)(2)
'Uncomment this section to sum from a consistent row: in this example, row 2
'And comment out the lower section of code
'With myCell
' .Formula = "=SUM(" & _
' Range(.Offset(-1, 0), _
' Cells(2, .Column)).Address(False, False) & ")"
'End With

'Use this to sum the block up from the lowest cell
With myCell
.Formula = "=SUM(" & _
Range(.Offset(-1, 0), _
.Offset(-1, 0).End(xlUp)).Address(False, False) & ")"
End With
End Sub

HTH,
Bernie
MS Excel MVP

"Danny" wrote in message
...
Hi,

Before I posted this question, I researched on similar questions on this NG
and I didn't get the answer I needed. All I need is a formula that would sum
numbers above the active cell.

This is the formula I have.

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

It works but it is only good if there are four or less numbers above it.

What I need is a formula for variable numbers to add.

Thank you.