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

 

[belodelokelo]

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.

Thanks for the detailed response. It’s clear.

Do you have a suggestion on how I can make it work based on the posted file and intention?

 

[Joe4]

OK, I have downloaded the file.

Let me make sure I understand exactly what it is you are trying to do. Tell me if this is correct:
On Sheet1, you will manually enter a name in column E.
After that value is entered, you want to look at the RAW sheet, and find the record with the matching Name and Department field, and return the Comment field from that row column F on Sheet1 of the row you just entered the name in column E for.
Is that correct?

Also, are you trying to return the value from the Comment column, or actually return the value from the Comment box in that cell (trying to return cell value or comment value)?

 

[belodelokelo]

OK, I have downloaded the file.

Let me make sure I understand exactly what it is you are trying to do. Tell me if this is correct:
On Sheet1, you will manually enter a name in column E.
After that value is entered, you want to look at the RAW sheet, and find the record with the matching Name and Department field, and return the Comment field from that row column F on Sheet1 of the row you just entered the name in column E for.
Is that correct?

Also, are you trying to return the value from the Comment column, or actually return the value from the Comment box in that cell (trying to return cell value or comment value)?

Thank you for the help. Actually, the input will be by Department [tax, legal etc]. This input will return a name on D sourced from RAW. Then what you said is correct.

I actually want to return the Comment copied as it is on RAW. Not the value.

 

[belodelokelo]

Thank you for the help. Actually, the input will be by Department [tax, legal etc]. This input will return a name on D sourced from RAW. Then what you said is correct.

I actually want to return the Comment copied as it is on RAW. Not the value

*This input (on D) will return a name on E sourced from RAW.

 

[Joe4]

OK, I think I would know how that code would have to be written, but it looks like there is another problem/challenge.
It appears you are using Excel 365, and the new "threaded comments" feature.

From this link here: The difference between threaded comments and notes - Microsoft Support., you can see what it says about it:

Prior to 365, you could pull Comments via VBA using a line of code like this:

Range("A1").Comment.Text

However, what was "Comments" prior to 365 are now "Notes" in 365.
"Comments" are indicated with the purple shape in the upper right hand corner of the cell. "Notes" have an orange triangle, i.e.

The VBA method shown above works to pull "Notes" in 365, but not "Comments".
I have been searching the internet, and I have not been able to figure out a way to pull these new 365 - Type "Comments" yet. I have found some complex or convoluted methods I don't quite understand.

So, here is the code that I came up with so far. Basically, it runs when D on Sheet1 is manually updated. We would use the "Worksheet_Change" event procedure running on/against Sheet1 to do this.
Here is the code I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws1 As Worksheet
    Dim wsRAW As Worksheet
    Dim dep As String
    Dim auto As String
    Dim act As String
    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Capture current worksheet
    Set ws1 = ActiveSheet
'   Set RAW sheet
    Set wsRAW = Sheets("RAW")
    
'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   See if update in column D in row 6 or below
    If Target.Column = 4 And Target.Row >= 6 Then
'       Capture values from column D and E
        dep = Target.Value
        auto = Target.Offset(0, 1).Value
'       Find last row with data on RAW sheet
        wsRAW.Activate
        lr = wsRAW.Cells(wsRAW.Rows.Count, "A").End(xlUp).Row
'       Loop through rows to find matching values on "RAW" sheet
        For r = 2 To lr
            If (wsRAW.Cells(r, "A") = dep) And (wsRAW.Cells(r, "B") = auto) Then
'               If match if found, pull comments from cell C and exit loop
'***************act = ...  '*********THIS IS THE PART WE NEED HELP ON HERE!
                Exit For
            End If
        Next r

'       If matching value found, update column C
        ws1.Activate
        If act <> "" Then
            Target.Offset(0, 2) = act
        Else
            MsgBox "No matching value found on RAW sheet"
        End If
    End If
    
    Application.ScreenUpdating = True
        
End Sub

We just need to figure out this one line here:

'***************act = ...  '*********THIS IS THE PART WE NEED HELP ON HERE!

I will reach out to some MVP friends to see if they know how to fix that one part.

 

[belodelokelo]

OK, I think I would know how that code would have to be written, but it looks like there is another problem/challenge.
It appears you are using Excel 365, and the new "threaded comments" feature.

From this link here: The difference between threaded comments and notes - Microsoft Support., you can see what it says about it:
View attachment 88884

Prior to 365, you could pull Comments via VBA using a line of code like this:

Range("A1").Comment.Text

However, what was "Comments" prior to 365 are now "Notes" in 365.
"Comments" are indicated with the purple shape in the upper right hand corner of the cell. "Notes" have an orange triangle, i.e.
View attachment 88885

The VBA method shown above works to pull "Notes" in 365, but not "Comments".
I have been searching the internet, and I have not been able to figure out a way to pull these new 365 - Type "Comments" yet. I have found some complex or convoluted methods I don't quite understand.

So, here is the code that I came up with so far. Basically, it runs when D on Sheet1 is manually updated. We would use the "Worksheet_Change" event procedure running on/against Sheet1 to do this.
Here is the code I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws1 As Worksheet
    Dim wsRAW As Worksheet
    Dim dep As String
    Dim auto As String
    Dim act As String
    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Capture current worksheet
    Set ws1 = ActiveSheet
'   Set RAW sheet
    Set wsRAW = Sheets("RAW")
   
'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   See if update in column D in row 6 or below
    If Target.Column = 4 And Target.Row >= 6 Then
'       Capture values from column D and E
        dep = Target.Value
        auto = Target.Offset(0, 1).Value
'       Find last row with data on RAW sheet
        wsRAW.Activate
        lr = wsRAW.Cells(wsRAW.Rows.Count, "A").End(xlUp).Row
'       Loop through rows to find matching values on "RAW" sheet
        For r = 2 To lr
            If (wsRAW.Cells(r, "A") = dep) And (wsRAW.Cells(r, "B") = auto) Then
'               If match if found, pull comments from cell C and exit loop
'***************act = ...  '*********THIS IS THE PART WE NEED HELP ON HERE!
                Exit For
            End If
        Next r

'       If matching value found, update column C
        ws1.Activate
        If act <> "" Then
            Target.Offset(0, 2) = act
        Else
            MsgBox "No matching value found on RAW sheet"
        End If
    End If
   
    Application.ScreenUpdating = True
       
End Sub

We just need to figure out this one line here:

'***************act = ...  '*********THIS IS THE PART WE NEED HELP ON HERE!

I will reach out to some MVP friends to see if they know how to fix that one part.

This is amazing. Thank you very much for the work.

Couldn’t we do it with simple copy/paste?

 

[Joe4]

Couldn’t we do it with simple copy/paste?

We could, if you just want to copy over the Comment as a Comment in the other sheet.
That would look like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws1 As Worksheet
    Dim wsRAW As Worksheet
    Dim dep As String
    Dim auto As String
    Dim act As String
    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Capture current worksheet
    Set ws1 = ActiveSheet
'   Set RAW sheet
    Set wsRAW = Sheets("RAW")
    
'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   See if update in column D in row 6 or below
    If Target.Column = 4 And Target.Row >= 6 Then
'       Capture values from column D and E
        dep = Target.Value
        auto = Target.Offset(0, 1).Value
'       Find last row with data on RAW sheet
        wsRAW.Activate
        lr = wsRAW.Cells(wsRAW.Rows.Count, "A").End(xlUp).Row
'       Loop through rows to find matching values on "RAW" sheet
        For r = 2 To lr
            If (wsRAW.Cells(r, "A") = dep) And (wsRAW.Cells(r, "B") = auto) Then
'               If match if found, pull comments from cell C and exit loop
                wsRAW.Cells(r, "C").Copy Target.Offset(0, 2)
                Exit For
            End If
        Next r

    Application.ScreenUpdating = True
        
End Sub

 

[belodelokelo]

Thank you amazing

We could, if you just want to copy over the Comment as a Comment in the other sheet.
That would look like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws1 As Worksheet
    Dim wsRAW As Worksheet
    Dim dep As String
    Dim auto As String
    Dim act As String
    Dim lr As Long
    Dim r As Long
  
    Application.ScreenUpdating = False
  
'   Capture current worksheet
    Set ws1 = ActiveSheet
'   Set RAW sheet
    Set wsRAW = Sheets("RAW")
  
'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
  
'   See if update in column D in row 6 or below
    If Target.Column = 4 And Target.Row >= 6 Then
'       Capture values from column D and E
        dep = Target.Value
        auto = Target.Offset(0, 1).Value
'       Find last row with data on RAW sheet
        wsRAW.Activate
        lr = wsRAW.Cells(wsRAW.Rows.Count, "A").End(xlUp).Row
'       Loop through rows to find matching values on "RAW" sheet
        For r = 2 To lr
            If (wsRAW.Cells(r, "A") = dep) And (wsRAW.Cells(r, "B") = auto) Then
'               If match if found, pull comments from cell C and exit loop
                wsRAW.Cells(r, "C").Copy Target.Offset(0, 2)
                Exit For
            End If
        Next r

    Application.ScreenUpdating = True
      
End Sub

Thank this is great.

Two questions:
1/ This code will work fast and well for 1000 rows for example?
2/ The other suggestions of yours for the comment, is faster resource-wise? It basically creates a comment every time instead of copying the already created one? Which way you suggest?

 

[Joe4]

1. Since the code only runs on the row being updated (one row at a time), it shouldn't slow down too much for a lot of data. It does have to search through the rows, but since it is only making one update, it should be pretty speedy.

2. I am not sure what you are asking for the second question. Given the choice though, I would recommend using "Notes" instead of "Comments", if possible, and if you want it to appear in the cell of the destination instead of a Comment/Note. Notes are much easier to work with, from a VBA standpoint.

 

[belodelokelo]

1. Since the code only runs on the row being updated (one row at a time), it shouldn't slow down too much for a lot of data. It does have to search through the rows, but since it is only making one update, it should be pretty speedy.

2. I am not sure what you are asking for the second question. Given the choice though, I would recommend using "Notes" instead of "Comments", if possible, and if you want it to appear in the cell of the destination instead of a Comment/Note. Notes are much easier to work with, from a VBA standpoint.

2. The question was that you suggested a way to do this that you said equites you to ask MVPs etc. That way of doing it is faster vs commenting?

What I actually want to achieve here is to mention people on the shared file. This is not possible based on what I have read with VBA. Is this the case? If it was possible it would replace the copy/paste part of the code.