sum code based on variable range

[The Idea Dude]

Hey everyone!

Lets say I am arriving at some cells and I dont know what they are going to be before I arrive at them, how do I do a sum under the following circumstances using the following example.

A10 is the active cell and where the formula has to go
A9 contains a number
A8 contains a number
A7 Contains a number
A6 is empty (It will be the active cell as my loop progresses and I will be back at the same situation as with A10 at the moment but I may not necessarily have 3 cells above, i might only have 1 with a number in it)

So with the above, A10 would need to sum A9, A8, A7 and thats it because A6 is empty. So in essence the code needs to sum up from the active cell until it reaches an empty cell.

Thanks

 

[jindon]

Is it

With Range("a10")
    .Formula = "=sum(r" & .End(xlUp).Row & "c:r[-1]c)"
End With

?

 

[The Idea Dude]

Is it

With Range("a10")
    .Formula = "=sum(r" & .End(xlUp).Row & "c:r[-1]c)"
End With

?

Thanks for the quick reply. This is only giving me the cell above. I thought it might be something like this but if i select A10 and then ctrl shift up it only goes up 1 cell at a time

Any other suggestions or feedback.

Thanks

 

[The Idea Dude]

Hmm, at closer inspection from A10 I need to ctrl Shift up twice

 

[jindon]

Hummm then how about

With Range("a10")
    .Formula = "=sum(r" & .Offset(-1).End(xlUp).Row & "c:r[-1]c)"
End With

 

[The Idea Dude]

Hummm then how about

With Range("a10")
    .Formula = "=sum(r" & .Offset(-1).End(xlUp).Row & "c:r[-1]c)"
End With

Works like a charm, You are a legend Thank You!!

 

[Peter_SSs]

Works like a charm, You are a legend Thank You!!

Unless there is just a single cell with data above the active cell. Is that a possibility? For example, applying the suggestion to A10 in this sheet produced the incorrect formula shown:

Sum vba

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">2</TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">2</TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">2</TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">2</TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">2</TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">4</TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A10</TD><TD>=SUM(A$7:A9)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[The Idea Dude]

Unless there is just a single cell with data above the active cell. Is that a possibility? For example, applying the suggestion to A10 in this sheet produced the incorrect formula shown:

Sum vba

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">2</TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">2</TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">2</TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">2</TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">2</TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">4</TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A10</TD><TD>=SUM(A$7:A9)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Hmm, works for me

 

[Peter_SSs]

Hmm, works for me

You mean it works when the activecell is A10 in this sheet and the code is run?

Excel Workbook
	A	B
3
4	2
5	2
6	2
7	2
8
9	2
10
Sum vba

 

[jindon]

change to

With Range("a10")
    .Value = "huh?"
    .Formula = "=sum(r" & .End(xlUp).Row & "c:r[-1]c)"
End With