Active cell calculation in Excel

Hello

If I understand correctly, with a macro to put in the sheet module (modify the constant plage)

Const plage = "A1:A10" Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range(plage)) Is Nothing Then If Target.Row = 1 Then Exit Sub Else Target.Offset(1, 0).Value = Target.Value + Target.Offset(-1, 0) End If End If End Sub

Good luck

Phew! Thanks for this start of a response. It’s time for me to dive into the macros. So I created this macro (Developer tab > Macro). I named the macro “KM,” I copied your code. I closed to go back to my sheet. Then I clicked on “Macros,” and KM disappeared… Moreover, what does “A1:A10” correspond to?

Thank you for following up!

re

1. first we should check if I understood your question correctly: selecting a cell in the range triggers the addition of the active cell and the cell above, and puts the result in the cell below (from the active cell)

2. if so
2.1. the macro must be copied exactly as is into the module of the sheet where it should apply (right-click the tab / view code)
2.2. the macro will apply here, only on the range "A1:A10", you can modify this range according to your needs

good luck

Okay okay ...
Thanks ccm81

For simplicity, I’m attaching part of my file:

http://cjoint.com/?BEilr5NJzAT

When I click on E3, I want the result of the calculation E3-C3 to be displayed in A1
When I click on E4, I want the result of the calculation E4-C4 to be displayed in A1

Here is the concrete result to obtain.
It’s probably easier with an example

re

1. I can’t read your file, if you need to send it, save it in Excel 2003 format
2. your request is not quite the same anymore!
3. the code is even simpler (applied to the range E1:E10)

Const plage = "E1:E10" Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range(plage)) Is Nothing Then Range("A1").Value = Target.Value - Target.Offset(0, -2) End If End Sub

good luck

Hello Eric

apparently, #4, touroul wants the result of his subtraction in A1 no matter which cell is clicked in column E

enjoy your meal

Here I am again, sorry I went off to put my kids to bed...
Indeed, it’s the introduction of the active cell variability that makes the problem complex.
Here is my simplified Excel 2003 version file:
http://cjoint.com/?BEimv4vhxJb
It’s true, I had stupidly made the problem more complicated than it really is!

Well, I’m stuck on a simple problem:
I don’t know how to record a macro:
I’ve shown the Developer tab, I click Macros, I name it "KM", I modify its content with the provided code, I save the file, I close VBA, and once I’m in my sheet, my macro can’t be found.
OK, I have some progress to make, but right now I’m depressed.
I’d love to be able to create Victoire (especially May 8 ...)
Help!

re

1. you didn’t read my post 3 (2.1.) properly about the sheet module
2. your updated file (right-click on the sheet tab / view code and modify the range)
3. we can also name the E3:Exx range dynamically to avoid having to modify the constant (an added row is automatically accounted for)
4. nothing to be depressed about
5. good luck with the "creation" of Victory

bon continuation

WOW! It works! I chose the second code. I still need to adapt it to my sheet. I’m working on it and I’ll reply to you tonight. I don’t have much to report in return for your help, except my gratitude for this free and disinterested hand. I promise to tell you where I am. You cannot imagine the pleasure I get from progressing in Excel.

Well, I just spent 2 hours on it, with no success. I had the naïveté to think that by proposing a simplified table, I would manage to adapt the code to the original table ... mistake! I can't get it right. This time, I’m attaching the full file, with a few explanations about the goal: Excel 2003 version: http://cjoint.com/?BEiqDs5VXNR Excel 2010 version with macros: http://cjoint.com/?BEiqEQMVVFj

So here is the precise aim of the manipulation, which should make my life easier. First thing: I don’t want to clutter my table with three columns containing the aforementioned calculation, knowing that I jot it down by hand when I’m in the car. That’s why there is a “floating” calculation in A1:
- On click of a cell in column F3 to F5000, display in A1 the calculation F3-C3 (F4-C4, etc ...)
- On click of a cell in column L3 to L5000, display in A1 the calculation L3-F3 (L4-F4, etc ...)
- On click of a cell in column P3 to P5000, display in A1 the calculation P3-L3 (P4-L4, etc ...).

There you go! If this piece of code isn’t too hard to write, here’s what’s going to drive you crazy with it! Thanks in advance.

My setup: Windows 7 64-bit, Excel 2010.

re

the code is of the same type for each of the 3 affected ranges

V1. with fixed ranges (lines 3 or 5 to 5000 - one click after the last filled cell yields 0)
https://www.cjoint.com/?0EirWQyvEmh

V2. with dynamic ranges (one click after the last filled cell yields nothing)
https://www.cjoint.com/?0EiseeSABUg

Remainder to be checked, of course ....

have a good continuation

Wonderful, almost the exact result to obtain! A big thank you to you. Two comments however:

1- The calculation does not work in column P if there is no second step, that is, if column L is empty.
Could we add a condition to compute Px-Fx in column P if Lx is empty?

2- In V2, when I select a column containing Km (F, L, P), I get the following error message:
-------------
Runtime Error '13'
Type Mismatch
-------------
Perhaps it is because the code is not defined up to the last cell of the column? (the 1,048,576th row)

I promise after that I will stop asking for help, you have already done a lot for me!
Here is the new version of the file: http://cjoint.com/?BEitmnI9dg6
Marc

like this?

https://www.cjoint.com/?0EitxXA7kDr

RQ. i saw that you set all lines from the start to 5, is that what you want?

enjoy your meal

Dear CCM81

Bon appétit to you as well, at our place it’s a Chinese caterer, mmmmh!

If the calculations start at line 5, it doesn’t matter (these are my beginnings in this position ...)

Good news: there are no more problems, that’s what I was aiming for at 100%

I can only thank you and bow to the talent!

If I can return the favor in turn (HTML, Windows, job search in biology, ...) don’t hesitate.

See you soon! I’ll post a final reply after a few days of use.

Have a good evening
Marc

I have another request ...
In the file: https://www.cjoint.com/?BEptCHJTkdi
in case of absent data in columns F and L, can you implement in A1 the calculation: (Px-Cx)/2 ?
Thanks in advance !

Marc

voilà ... en principe
https://www.cjoint.com/?0Epvct9XRIx

bonne soirée

Once again, it’s perfect.

Fast service too.

Have a good evening, I’m going to put down my 3 PCs that are eating up my time and go watch a movie...

Have a good evening to you and yours, and thank you again.

Marc