troubleshooting Question
VBA: how to run a macro when certain variable cells change in Excel
Hello everybody.
I've been dealing with how to run a macro when certain cells change in Excel: I mean, under certain conditions a human being could manually change a value in a cell, and then, this change should trigger a recalculation in another cell. I'd like to precise that in my workbook, for internal reasons, it is no possible to use formulas.
Anyway, up to a certain point, the support Microsoft is clear.
https://support.microsoft.
In particular, if I know in advance the range that has to cause a recalculation, no problem:
Now, my task is harder.
Suppose I've a blank sheet with just a button to run a macro (see attachment "Recalculate-1").
Before running the macro "Update", i don't know what will be the result in terms of populated area.
Suppose, after the running, the new situation as in the second attachment ("Recalculate-2).
I need the green cells triggering recalculation, identified as follows:
- for each value "6" on column B, the green cells have to be - in the same row - from column D to the last populated column in the sheet (in this case, F);
- in event of inserting manually a new value in the green cells, recalculate the sum in column C of the same row (so, suppose I change manually the value in cell E19 from 150 to 100, then the new value in C19 will be 400).
Could anyone help me?
recalculate-1.bmp
recalculate-2.bmp
I've been dealing with how to run a macro when certain cells change in Excel: I mean, under certain conditions a human being could manually change a value in a cell, and then, this change should trigger a recalculation in another cell. I'd like to precise that in my workbook, for internal reasons, it is no possible to use formulas.
Anyway, up to a certain point, the support Microsoft is clear.
https://support.microsoft.
In particular, if I know in advance the range that has to cause a recalculation, no problem:
Set KeyCells = Range("A1:C10")
Now, my task is harder.
Suppose I've a blank sheet with just a button to run a macro (see attachment "Recalculate-1").
Before running the macro "Update", i don't know what will be the result in terms of populated area.
Suppose, after the running, the new situation as in the second attachment ("Recalculate-2).
I need the green cells triggering recalculation, identified as follows:
- for each value "6" on column B, the green cells have to be - in the same row - from column D to the last populated column in the sheet (in this case, F);
- in event of inserting manually a new value in the green cells, recalculate the sum in column C of the same row (so, suppose I change manually the value in cell E19 from 150 to 100, then the new value in C19 will be 400).
Could anyone help me?
recalculate-1.bmp
recalculate-2.bmp
Learn from the best
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
This award recognizes a member of Experts Exchange who has made outstanding contributions to the community within their first year as an expert. The Rookie of the Year is awarded to a new expert who has the highest number of quality contributions.