Capturing Cell Comments based on Vlookup?

gregtal

New Member
Joined
Apr 21, 2013
Messages
4
Posting this with the hope that I am not the first person to come up with this idea, and I hope I can be as clear and concise as possible in explaining it...

On worksheet 1, I have a set of data rows, arranged by location. On worksheet 2, I have laid out cells with vlookups to capture the various pieces of that data, based on location chosen from a cell list.

Is it possible to construct a macro or VBA that will copy any comments associated with cells on worksheet 1 related to the vlookup value selected on worksheet 2?

The end result should be that, when I selected a different location from the list on worksheet 1, the values relating to that location populate from worksheet 2 to worksheet 1(they do already), as do any comments that may be in any of those selected cells (they don't).

Thanks for any help hat comes my way!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Do you want comments associated with the cell in sheet1 that holds the lookup value or the cell in sheet1 that holds the value that is returned by the lookup? where do you want to place the comments? Do all the lookup formulas return values from the same column on sheet1 ....... ?
 
Upvote 0
Let me try this again - thanks for your patience...

Worksheet 1 contains my raw data and comments

Worksheet 2 contains a validation list comprised of the values in the first column of worksheet 1. It also contains cells that use the validation list selection to execute vlookup formulas to select and reference the correct row and cells in worksheet 1 to populate the worksheet 2 cells.

The first part of this, the actual vlookup functions on worksheet 2, works as it should. When I change the selection in the worksheet 2 validation list and the vlookup pulls the correct cell from worksheet 1 and refreshes the corresponding worksheet 2 cells.

What I want to also happen is that, should there be a comment in the particular field being pulled from worksheet 2, that it is also captured and copied to the cell and that subsequent changed selections from the validation list result in refreshed comment status (in other wordss it doesn't retain the comment from the last cell referenced).

Hope this helps.
 
Upvote 0
Let me try this again - thanks for your patience...

Worksheet 1 contains my raw data and comments

Worksheet 2 contains a validation list comprised of the values in the first column of worksheet 1. It also contains cells that use the validation list selection to execute vlookup formulas to select and reference the correct row and cells in worksheet 1 to populate the worksheet 2 cells.

The first part of this, the actual vlookup functions on worksheet 2, works as it should. When I change the selection in the worksheet 2 validation list and the vlookup pulls the correct cell from worksheet 1 and refreshes the corresponding worksheet 2 cells.

What I want to also happen is that, should there be a comment in the particular field being pulled from worksheet 2, that it is also captured and copied to the cell and that subsequent changed selections from the validation list result in refreshed comment status (in other wordss it doesn't retain the comment from the last cell referenced).

Hope this helps.
That helps, but some more info would be needed. If I understand correctly, on sheet2 you have a cell (let's say A2 for example) with a validation drop down list. When you make a new selection from the drop down, some other cell (say B2 for example) is populated via a Vlookup referencing a table in sheet1. If the cell value from sheet1 that is returned to B2 via that formula has a cell comment attached to it, you would like that comment to show up somewhere in sheet2. You haven't said where/how you want the comment placed (as a comment in B2 or in a cell on sheet2, say C2 for example).

This can be done with a macro, but you would have to post some more information on the layout of the table in sheet1, which column is the return value coming from, the layout of sheet2, and how/where the comment from the source cell is to be incorporated in sheet2.
 
Upvote 0
So far, you've got it. Let's say that, on worksheet 2, in cell C2, I have a vlookup formula that pulls a value from cell D5 on worksheet 1. If there is a comment planted in cell D5, then I want that comment to be planted in cell C2 on worksheet 2, along with the value captured by the vlookup formula. I don't want it pasted to a different cell. i want to be able to hover over C2 and see the very comment that exists in Worksheet 1, cell D5.
 
Upvote 0
So far, you've got it. Let's say that, on worksheet 2, in cell C2, I have a vlookup formula that pulls a value from cell D5 on worksheet 1. If there is a comment planted in cell D5, then I want that comment to be planted in cell C2 on worksheet 2, along with the value captured by the vlookup formula. I don't want it pasted to a different cell. i want to be able to hover over C2 and see the very comment that exists in Worksheet 1, cell D5.
On sheet2, what column is the validation drop down in and what row is the first cell with a drop down? Will the vlookup always be returning a value from column D on sheet1?
 
Upvote 0
Worksheet 1 -
The values used in the dropdown list are in column T and the cell that actually references it is B4.
Then there are numerous cells that contain vlookup fomulas, all of which depend on B4 as the lookup value, but which are all pulling data from different columns of worksheet 2

When the value in cell B4 changes, the vlookups still pull the information from the same column, just a different row, corresponding to the vlookup value

And why do I have to log in two or three times to get things to post?
 
Upvote 0
Worksheet 1 -
The values used in the dropdown list are in column T and the cell that actually references it is B4.
Then there are numerous cells that contain vlookup fomulas, all of which depend on B4 as the lookup value, but which are all pulling data from different columns of worksheet 2

When the value in cell B4 changes, the vlookups still pull the information from the same column, just a different row, corresponding to the vlookup value

And why do I have to log in two or three times to get things to post?
Now I'm confused again. I thought the cell with the drop down you want to trigger all the action from is on sheet2, but in the quote above it seems its B4 on sheet1.
I can write a macro to do what I think you want but only if I have some specifics about the 2 sheets involved.
Here's an example.
Sheet 2 has a drop down in cell B2 that contains a list of items in column A on sheet1. Cell C2 on sheet2 contains a lookup formula like this:
=VLOOKUP(B2,Sheet1!$A$2:$D$14,4,0)
which looks up the value in sheet2 cell B2 in column A of sheet1 and returns a value from column D on sheet1. The code below will run automatically whenever the drop down in cell B2 of sheet2 is accessed by the user. If there is a cell comment present in the lookup return cell in column D of sheet1, then that comment will appear in sheet2 cell C2 as a cell comment along with the value returned by the lookup formula appearing as a value in cell C2 sheet2.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim fWhat As Variant, fCell As Range
Set Target = Target.Cells(1, 1)
Target.Offset(0, 1).ClearComments
If Not Intersect(Target, Columns("B")) Is Nothing Then
    fWhat = Target.Offset(0, 1).Value
    With Sheets("Sheet1").Columns("D")
        Set fCell = .Find(fWhat, [D1], xlFormulas, xlWhole, xlNext)
        If Not fCell Is Nothing Then
            If Not fCell.Comment Is Nothing Then
                Target.Offset(0, 1).AddComment fCell.Comment.Text
            End If
        End If
    End With
End If
End Sub
From this example you can see the specifics that are required to construct the macro. Can you give me an analogous set of specifics for your two worksheets?
 
Upvote 0
This has been helpful but I'm having issues. I've got pictures in the commentbox as a Fill on sheet2 ColumnA. In sheet 1 Columns A:F are values that are Vlookup to sheet 2 column A to copy the pictures from Sheet2 to SheetA. The sheets have a Header row with ActiveX buttons in A1.
All works great until I save, then Load. On load there are errors, it doesn't load the ActiveX buttons on sheet1 & all comments on sheet1 are gone.

If I only have it copy comments to column A then all is fine but I would like A:K if possible
Code:
Function CopyComment()         

 Set Target = ActiveCell                
Dim fWhat As Variant, fCell As Range
If Target.Comment Is Nothing Then


    If Not Intersect(Target, Columns("A:K")) Is Nothing Then
        fWhat = Target.Value
        With Sheets("Qty_Master_List").Columns("A")
            Set fCell = .Find(fWhat, [A2], xlFormulas, xlWhole, xlNext)
         If Not fCell Is Nothing Then
                If Not fCell.Comment Is Nothing Then
                fCell.Copy
                    Target.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False
                    End If
         End If
        End With
    End If
    
End If




End Function
Code:
Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
CopyComment
end sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top