Action on cell C1 when cell B1 changes with function

[belodelokelo]

Dear All,

I have this function on cell B1:

=FILTER('RAW'C:C,'RAW'T:T=A1)

Basically, if on A1 I write something it checks a database on "RAW" sheet and returns the relevant value to B1.

Then what I want for the below code is auto-detect any change on column B and do something on column C. I used for simplicity the .Value = "Works!" function since it's important for me now to have the explained functionality.

Briefly if I type on A then it filters B based on the table and does something else on C. So with one enter to do both.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Dim Xrg As Range

Set Xrg = Range("B:B")
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
xOffsetColumn = 2
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng

If Not Intersect(Xrg, Range("B:B")) Is Nothing Then
Rng.Offset(0, xOffsetColumn).Value = "Works!"
Else
Rng.Offset(0, xOffsetColumn).Value = "No"
End If


Next
Application.EnableEvents = True
End If
End Sub

 

[Joe4]

Welcome to the Board!

The "Worksheet_Change" event procedure is only triggered upon manual data entry. It is not triggered due to changes in values brought about by formulas.

There is a "Worksheet_Calculate" event procedure that is triggered upon values changes due to formulas. However, there is a big caveat to that. "Worksheet_Calculate" triggers when ANY value on the entire sheet is re-calculated AND it cannot tell which cell's value changed - only that some value was re-calculated somewhere on the sheet. So with "Worksheet_Calculate", you do not have the ability to watch a particular range for a change.

You may be able to use "Worksheet_Change" if you watch for a change to cell A1 instead of column B, and go from there.

 

[belodelokelo]

Welcome to the Board!

The "Worksheet_Change" event procedure is only triggered upon manual data entry. It is not triggered due to changes in values brought about by formulas.

There is a "Worksheet_Calculate" event procedure that is triggered upon values changes due to formulas. However, there is a big caveat to that. "Worksheet_Calculate" triggers when ANY value on the entire sheet is re-calculated AND it cannot tell which cell's value changed - only that some value was re-calculated somewhere on the sheet. So with "Worksheet_Calculate", you do not have the ability to watch a particular range for a change.

You may be able to use "Worksheet_Change" if you watch for a change to cell A1 instead of column B, and go from there.

Thank you very much for the response.

I would actually need the whole A column. Does this work with "Worksheet_Change"?

The functionality I want to emulate is basically this: How to automatically run macro when cell formula result changes? which works with "Worksheet_Calculate" only on A.

Private Sub Worksheet_Calculate()
'Updateby Extendoffice
Dim Xrg As Range
Set Xrg = Range("C2:C8")
If Not Intersect(Xrg, Range("C2:C8")) Is Nothing Then
Macro1
End If
End Sub

The issue is that on "Macro1" what I want is to change only the cell on the same raw as the initiated change. If I change the "Worksheet_Change" to "Worksheet_Calculate" can this work?

I would really appreciate your help community.

 

[Joe4]

I would actually need the whole A column. Does this work with "Worksheet_Change"?

You can detect that changes have been manually made to any cells in column A like this, for example:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Range("A:A"))
    
    If Not (rng Is Nothing) Then
        For Each cell In rng
            MsgBox "Value changed in cell " & cell.Address(0, 0)
        Next cell
    End If
    
End Sub

However, as I said, this only works for manual updates to column A. It would not work on any formulas in column A.

The functionality I want to emulate is basically this: How to automatically run macro when cell formula result changes? which works with "Worksheet_Calculate" only on A.

Compare the first line of "Worksheet_Change" to Worksheet_Calculate":

Private Sub Worksheet_Change(ByVal Target As Range)

Private Sub Worksheet_Calculate()

Notice how "Worksheet_Change" has a range parameter named "Target". This is the range that was just manually updated that triggered the code to run.
This is what allows you to identify exactly which cell was changed.

Now notice how "Worksheet_Calculate" does not have this parameter. Since it does not have it, you cannot tell which formula was re-calculated.
This code is triggered when a re-calculation happens anywhere on the sheet. You have no way of knowing exactly what value changed using "Worksheet_Calculate".
So you cannot do something like a "Worksheet_Calculate only on column A". It is not possible.

The best that you can do is watch the ranges referenced in the formula, if they are updated manually, with a "Worksheet_Change" event procedure.
So if your formula in cell B1 is something like:

=A1

and cell A1 is being updated manually, then you could watch cell A1 for a manual update with a "Worksheet_Change" event procedure.

The issue is that on "Macro1" what I want is to change only the cell on the same raw as the initiated change. If I change the "Worksheet_Change" to "Worksheet_Calculate" can this work?

As described in detail above, "Worksheet_Change" and "Worksheet_Calculate" do not work the same.
So you cannot just change one-for-the-other.

 

[belodelokelo]

Thanks

You can detect that changes have been manually made to any cells in column A like this, for example:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
    Set rng = Intersect(Target, Range("A:A"))
   
    If Not (rng Is Nothing) Then
        For Each cell In rng
            MsgBox "Value changed in cell " & cell.Address(0, 0)
        Next cell
    End If
   
End Sub

However, as I said, this only works for manual updates to column A. It would not work on any formulas in column A.


Compare the first line of "Worksheet_Change" to Worksheet_Calculate":

Private Sub Worksheet_Change(ByVal Target As Range)

Private Sub Worksheet_Calculate()

Notice how "Worksheet_Change" has a range parameter named "Target". This is the range that was just manually updated that triggered the code to run.
This is what allows you to identify exactly which cell was changed.

Now notice how "Worksheet_Calculate" does not have this parameter. Since it does not have it, you cannot tell which formula was re-calculated.
This code is triggered when a re-calculation happens anywhere on the sheet. You have no way of knowing exactly what value changed using "Worksheet_Calculate".
So you cannot do something like a "Worksheet_Calculate only on column A". It is not possible.

The best that you can do is watch the ranges referenced in the formula, if they are updated manually, with a "Worksheet_Change" event procedure.
So if your formula in cell B1 is something like:

=A1

and cell A1 is being updated manually, then you could watch cell A1 for a manual update with a "Worksheet_Change" event procedure.


As described in detail above, "Worksheet_Change" and "Worksheet_Calculate" do not work the same.
So you cannot just change one-for-the-other.

Thanks for the response!

The case is actually that the below code automatically detects any change on the whole column A.

Private Sub Worksheet_Calculate()
'Updateby Extendoffice
Dim Xrg As Range
Set Xrg = Range("A:A")
If Not Intersect(Xrg, Range("A:A")) Is Nothing Then
Macro1
End If
End Sub

But I what additionally to this is to be able to know the row in which the change occurred so that I change the column next to that cell. So if A4 changes, I will act on B4 etc.

It's possible to do this somehow.

 

[Joe4]

The case is actually that the below code automatically detects any change on the whole column A.

Not it actually does NOT!!!

I will say this a third time, and as try to be crystal clear.
Worksheet_Calculate will fire whenever there is ANY calculation ANYWHERE on the sheet. And Excel CANNOT identify/tell you exactly where that recalculation occurred.
There is no way "Worksheet_Calculate" can be restricted to only run when a value in column A is re-calculated.
So, yes, if there is a re-calculation in column A, the code will run. But it will also run if there is a re-calculation anywhere else on your entire sheet!

There is also a very big flaw with the logic in your code.
Look what you are doing here:

Set Xrg = Range("A:A")
If Not Intersect(Xrg, Range("A:A")) Is Nothing Then

All you are doing is comparing a range to exactly itself!

If you substitute the "Xrg" variable for its actually value, what your code is really saying is:

If Not Intersect(Range("A:A"), Range("A:A")) Is Nothing Then

So what it is really asking is "does column A intersect with column A?"
That will ALWAYS be true, as anything is always equal to itself.

You have quite frankly confused two very different things here ("Worksheet_Change" and "Worksheet_Calculate"). As I said they do NOT work the same way.
The Intersect is typically used with the "Target" range parameter found in "Worksheet_Change" event procedures.
But as I said in my previous posts, "Worksheet_Calculate" event procedures do not have "Target" range parameters. So you would NOT use the Intersect method in that manner.

 

[belodelokelo]

Not it actually does NOT!!!

I will say this a third time, and as try to be crystal clear.
Worksheet_Calculate will fire whenever there is ANY calculation ANYWHERE on the sheet. And Excel CANNOT identify/tell you exactly where that recalculation occurred.
There is no way "Worksheet_Calculate" can be restricted to only run when a value in column A is re-calculated.
So, yes, if there is a re-calculation in column A, the code will run. But it will also run if there is a re-calculation anywhere else on your entire sheet!

There is also a very big flaw with the logic in your code.
Look what you are doing here:

Set Xrg = Range("A:A")
If Not Intersect(Xrg, Range("A:A")) Is Nothing Then

All you are doing is comparing a range to exactly itself!

If you substitute the "Xrg" variable for its actually value, what your code is really saying is:

If Not Intersect(Range("A:A"), Range("A:A")) Is Nothing Then

So what it is really asking is "does column A intersect with column A?"
That will ALWAYS be true, as anything is always equal to itself.

You have quite frankly confused two very different things here ("Worksheet_Change" and "Worksheet_Calculate"). As I said they do NOT work the same way.
The Intersect is typically used with the "Target" range parameter found in "Worksheet_Change" event procedures.
But as I said in my previous posts, "Worksheet_Calculate" event procedures do not have "Target" range parameters. So you would NOT use the Intersect method in that manner.

Could you suggest what I can use to achieve the intended functionality?

 

[Joe4]

Can you show us some sample data, and explain exactly what it is that you want to happen?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[belodelokelo]

Thanks for the tip. I am on Mac so I will upload the full file here for you. Test_Excel.xlsm

Worksheet_Calculate will fire whenever there is ANY calculation ANYWHERE on the sheet. And Excel CANNOT identify/tell you exactly where that recalculation occurred.

On the above, not sure why you are so confident with this. Check my uploaded file. It works with the code mentioned above and ONLY the specified area triggers the Macro. Also the code was taken from here: How to automatically run macro when cell formula result changes?.

 

[Joe4]

On the above, not sure why you are so confident with this

What makes me so confident about this? Oh about 25 years of coding with VBA and understanding of how the different Event Procedures work!
If you read the comments in the article you referenced, you will see people mentioning the same thing that I did. You cannot always trust everything you read on the internet, sometimes people post erroneous things!

It is very easy to prove that there is flawed logic in that code and it does not work right.
Enter numbers in cells A1:A20 and in B1:B20.
And then in cell A1, enter the formula:

=A1*B1

and copy that formula all the way down to cell C20

Then add the following code to the worksheet module:

Private Sub Worksheet_Calculate()
'Updateby Extendoffice
    Dim Xrg As Range
    Set Xrg = Range("C2:C8")
    If Not Intersect(Xrg, Range("C2:C8")) Is Nothing Then
    Macro1
    End If
End Sub

Sub Macro1()
    MsgBox "Macro1 running"
End Sub

Now, if you change any value in cells A2:B8, the value in the corresponding row will update the value in column C, and the macro will run and you will get the message "Macro1 running".
Seems to be working OK so far.

But watch what happens when you update a cell in column A or B outside of that range. If you update cell A10, it will update the value in cell C10.
Since that is outside the range C2:C8, it should NOT trigger Macro1 to run -- BUT IT DOES!!! Try it and I see.

As I said numerous times now, Worksheet_Calculate cannot detect which cells formula was recalculated, no matter how much you want it to.
All it can tell you is that some cell somewhere on your sheet was recalculated.