Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
To achieve that, right click the Sheet Tab --> View Code --> Paste the following code into the opened code window.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim lr As Long, lc As Long
Dim rng As Range
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lc = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
If lr < 3 Or lc < 4 Then Exit Sub
On Error GoTo Skip
If Target.Column > 3 And Target.Row > 2 Then
If Cells(Target.Row, 2) = 6 Then
Application.EnableEvents = False
Set rng = Range(Cells(Target.Row, 4), Cells(Target.Row, lc))
Debug.Print rng.Address
Cells(Target.Row, 3) = Application.Sum(rng)
End If
End If
Skip:
Application.EnableEvents = True
End Sub
Yes, it works, but very slowly.
I mean: when the macro "Update" is called via the button, it takes several minutes to end the task.
Probably I've to stop some sort of loops created by the process Worksheet_Change.
I mean: when the macro "Update" is called via the button, it takes several minutes to end the task.
Probably I've to stop some sort of loops created by the process Worksheet_Change.
In the code underneath the Update button, you may disable to events so that the change event code will not be triggered when the Update code tries to write the data onto the Sheet.
To do that, in your Update code, add the following line in the beginning of the code...
If in your Update code, you are using Exit Sub statement anywhere in the code, please remember to enable the events by adding the above line of code before the Exit Sub line.
To do that, in your Update code, add the following line in the beginning of the code...
Application.EnableEvents = False
Application.EnableEvents = True
If in your Update code, you are using Exit Sub statement anywhere in the code, please remember to enable the events by adding the above line of code before the Exit Sub line.
Yes, well, now it takes few seconds.
The last step should be: once recalculated
Cells(Target.Row, 3) = Application.Sum(rng)
for each of the cells included in the ranges, if the cell change value then the new value of the underneath cell (maybe something like cell.offset(1, 0).value ) is cell.Offset(-1, 0).Value / cell.Value
The last step should be: once recalculated
Cells(Target.Row, 3) = Application.Sum(rng)
for each of the cells included in the ranges, if the cell change value then the new value of the underneath cell (maybe something like cell.offset(1, 0).value ) is cell.Offset(-1, 0).Value / cell.Value
The last step should be: once recalculatedIn which code? Do you mean in the Update code?
Cells(Target.Row, 3) = Application.Sum(rng)
In that case you need to tweak your Update code to display the correct sum in column C. Isn't it doing that in the existing Update code?
for each of the cells included in the ranges, if the cell change value then the new value of the underneath cell (maybe something like cell.offset(1, 0).value ) is cell.Offset(-1, 0).Value / cell.ValueI am not sure how the values are populated on the Sheet by the Update code. The proposed solution is based on the screenshot of the data you shared and it will produce the desired output once any value gets changed manually.
For your new calculations in the existing code, you may open a New Question by providing the enough description along with a sample file if required.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial