how to count total #s

[hh2017]

Hi, I have a simple question. I am trying to count the total #s in one dynamic cell. Saying A1 is the live cell and its value is changing between 1 and 0. I want A2 shows sums of A1. I tried A2=A2+A1 but it doesn't work. Please help, thanks!

[Jonmo1]

You need VBA Code for that.

Right click your sheet's tab, and select View Code
Paste the following

Please Login or Register  to view this content.

[hh2017]

Cool~ I found the view code is not activated either Macro. How can I activate them? Many thanks!

[Jonmo1]

Do you mean the option greayed out, or is it not there all ?
You probably need to enable macros (File - Options - Trust Center
Trust Center Settings
Macro Settings.

[hh2017]

I did "enable Macro and VBA" in Trust setting but still not work :-).

[hh2017]

yes, the option grayed out.

[Jonmo1]

Devine "still not work"

Is "View Code" Greyed out, or not there at all ?
Are you actually using Excel? Or an online version of software 'similar to' Excel ?

[hh2017]

Do I need to enable activeX :-)?

[Root_]

Just to mention: if A1 changes as a result of recalculation (i.e., has a formula) then the Worksheet.Change event will not work.

[hh2017]

Thanks for the help! I am using Excel 2010. And the view code is there just I couldn't access to it it was greyed out like you said :-).

[Jonmo1]

@Root_
Good point.

@hh2017
I think you need a thread in the VBA forum.
https://www.excelforum.com/excel-pro...ng-vba-macros/

[hh2017]

It seems Excel was not connecting to the internet. I reinstall the office, it was still not working. Any idea? Thank you~!

[hh2017]

I got the help from VBA forum. I actually didn't install visual basic because it was included in Microsoft access not in excel :-). I ran the code, it worked. Thanks so much!

[Jonmo1]

You're welcome.

[hh2017]

The code didn't work for live data. If cell A1 is a live cell, A2 didn't show anything. If I manually inserted values into A1, it worked. Any idea :-)? Thanks!

[Jonmo1]

You're going to need to clarify.

If you do this to the whole column A, you'll be creating a circular reference.
Say A2 changes, you add that to A3
Changing A3 triggers the code again, and adds that value to A4
changing A4 triggers the code again, and adds that value to A5
etc.

Do you perhaps add the values to column B?
If A2 changes, add that value to B2 ?
If A10 changes, add that value to B10 ?

[hh2017]

Hi, I have tested this code. It worked perfectly to accumulate A1 values into B1. But it didn't work for the live data. If cell A1 is a live cell, A2 didn't show anything. If I manually inserted values into A1, it worked.

Sorry I deleted the previous post :-). What I meant is just to do the same process for A2 and B2. I can extend the code in a simple way but I have hundreds of rows of data, so need to do something like "for i=i+1 next", just don't know how to write a VBA code :-).

Thanks!

[Jonmo1]

What do you mean by 'live' ?
Is A1 a formula ?
What is that formula?

You really need to discuss this in the VBA forum.
I don't think the issue is simple anymore, and it's not appropriate in the formulas forum.

Feel free to link to this thread when you make a new thread in the VBA forum.
Or maybe a moderator can just move this one over there.

[hh2017]

OK, I move it to VBA group. Thanks!