Total of a row

[MJA]

Hello Mr. Excel,

I have a serie of numbers that can vary, the
serie always starts in A1 and can go till Ax.
In the cell next to Ax I want the total.
How do I do that?

 

[Legacy 139]

In any event, I don't think much of your code.
All that is needed is :-

Sub For_Holland()
[A1].End(xlToRight)(1, 2).Value = Application.Sum(Range([A1], [A1].End(xlToRight)))
End Sub

 

[Juan Pablo González]

Another option, a Named Formula.

Select B1. This is IMPORTANT
Go to Insert, Names, Define.

In the first box type SumLeft, and in the Refers To, put this formula:

<pre>
=SUM(Sheet1!$A1:A1)
</pre>

Now, you can use it in a cell like this

=SumLeft

and it will Sum from Column A to the one on the left of that cell. It also adjustes automatically to insert or delete of columns.

 

[Legacy 139]

You said "unlike your original post, I was trying to answer the question". Oh? My original post certainly did answer the question! Have you tried the formula I suggested?

Still don't see the point of suggesting a macro when a simple worksheet formula does the job.

Was trying to help by offering improved code - thought you might be able to learn something.

 

[Legacy 139]

On 2002-02-21 18:37, NateO wrote:
The way I interpreted the question was that the placement of the sum formula, which varies, is equally as important as the function itself. To properly place the function, vba is required.

I do like your streamlined vba, the tone of the post is a little much for me. I get on here to help people, not to impress more experienced users necessarily.

It's all good. Cheers, Nate

You said "To properly place the function, vba is required."

This is simply NOT true! All that is required is to enter the formula once manually - just like entering any formula.

(You seem a trifle upset. If you promise not to cry, I'll buy you a lollipop.)

 

[Guest]

Tu ne parles pas anglais?

"The answer (total) must stand in the cell next to x1"

zuts alors

 

[Legacy 139]

On 2002-02-21 19:00, NateO wrote:
Tu ne parles pas anglais?

"The answer (total) must stand in the cell next to x1"

zuts alors

What is your point Miss Oliver?
When entering the formula, put it in cell x1.

I'm bored with this. Goodbye.

 

[Legacy 139]

On 2002-02-21 19:08, Tikas A. Planck wrote:
What is your point Miss Oliver?
When entering the formula, put it in cell x1.

I'm bored with this. Goodbye.

Should read :-

When entering the formula, put it in the cell next to cell x1.

 

[MJA]

On 2002-02-21 17:56, Tikas A. Planck wrote:
In any event, I don't think much of your code.
All that is needed is :-

Sub For_Holland()
[A1].End(xlToRight)(1, 2).Value = Application.Sum(Range([A1], [A1].End(xlToRight)))
End Sub

You're the man!
Thank you
You made my friday and weekend!
Bye

 

[Aladin Akyurek]

Juan,

It's an appropriate and judicious proposal.

Aladin

On 2002-02-21 17:58, Juan Pablo G. wrote:
Another option, a Named Formula.

Select B1. This is IMPORTANT
Go to Insert, Names, Define.

In the first box type SumLeft, and in the Refers To, put this formula:

<pre>
=SUM(Sheet1!$A1:A1)
</pre>

Now, you can use it in a cell like this

=SumLeft

and it will Sum from Column A to the one on the left of that cell. It also adjustes automatically to insert or delete of columns.

 

[Mark O'Brien]

On 2002-02-21 17:58, Juan Pablo G. wrote:
Another option, a Named Formula.

Select B1. This is IMPORTANT
Go to Insert, Names, Define.

In the first box type SumLeft, and in the Refers To, put this formula:

<pre>
=SUM(Sheet1!$A1:A1)
</pre>

Now, you can use it in a cell like this

=SumLeft

and it will Sum from Column A to the one on the left of that cell. It also adjustes automatically to insert or delete of columns.

I'm having problems getting this work, am I using it correctly?

I've went to B1, selected Insert|Name|Define, declared SumLeft as the name and copied you formula into the "refers to section".

TO test this I typed in number in A1:E1, then in F1 put in "=SumLeft". It's giving me "0".

I'd like to get this to work just out of interest. I hardly ever use Excel for data entry and summations etc and would like to get better at it.