vba, excel, sum different ranges

[Danny]

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.

[Bob Phillips]

How about

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


--
HTH

Bob Phillips

"Danny" <[email protected]> wrote in message
news:[email protected]...
> 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 Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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.
> >>
> >>
> >>
>
>
>