vba, excel, sum different ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
How about

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


--
HTH

Bob Phillips

Danny said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Bernie,

Perfect! Thanks a lot!

Bernie Deitrick said:
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 said:
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 said:
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


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.
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IF AND Nested Functions - Formula Not Working 0
Excel Excel Macro - Variable in a formula 1
Macro with sum function 1
Time Sum in Excel 1
Sum every Nth Row (almost working) 1
Excel How to add numbers in same cell? 2
Sum Column above current cell dynamically using function call 7
Excel VBA 1

Back
Top