Vlookup to Copy Comments in Excel (with a UDF)

We want to look up a value along with the comments (if any) in the range B5:C9. The lookup value is given in cell E5. We’d typically use a VLOOKUP formula to return a value associated with the lookup value along with comments if it contains any. However, the VLOOKUP function won’t copy the comments, so we will create a UDF that works with the same mechanism as the VLOOKUP function.

Step 1 – Open a New Module to Insert the VBA Code

Press Alt + F11 to open the VBA window.
Select Insert and choose New Module to create a new module as shown in the following picture.

Step 2 – Write VBA Code to Create UDF

Copy the following code using the copy button.

Function VLOOKUPCMT(lookup_value As Variant, table_array As Range, col_index_num As Long, range_lookup As Long) As Variant
Application.Volatile
Dim xReturn As Variant
Dim yCell As Range
xReturn = Application.Match(lookup_value, table_array.Columns(1), range_lookup)
If IsError(xReturn) Then
VLOOKUPCMT = "Not Found"
Else
Set yCell = table_array.Columns(col_index_num).Cells(1)(xReturn)
VLOOKUPCMT = yCell.Value
With Application.Caller
If Not .Comment Is Nothing Then
.Comment.Delete
End If
If Not yCell.Comment Is Nothing Then
.AddComment yCell.Comment.Text
End If
End With
End If
End Function

Paste the copied code into the blank module as shown in the picture below.

Step 3 – Apply the User-Defined Function to Copy Comments

Enter the lookup value (3) in cell E5.
Enter the following formula in cell F5. You will get the desired result as follows. The VLOOKUPCMT function works similarly to the VLOOKUP function.

=VLOOKUPCMT(E5,B5:C9,2,FALSE)

VBA Code Explanation:

Function VLOOKUPCMT(lookup_value As Variant, table_array As Range, col_index_num As Long, range_lookup As Long) As Variant
Here, the VLOOKUPCMT function will be the alternative to the VLOOKUP function in Excel. It has the same arguments as the VLOOKUP function.
Application.Volatile
Forces a recalculation whenever a calculation occurs in any other cells.
Dim xReturn As Variant
Dim yCell As Range
Declaring necessary variables.
xReturn = Application.Match(lookup_value, table_array.Columns(1), range_lookup)
Assigning values to the xReturn variable.
If IsError(xReturn) Then
VLOOKUPCMT = “Not Found”
Returns Not Found in case of errors.
Set yCell = table_array.Columns(col_index_num).Cells(1)(xReturn)
Assigns a cell reference to the yCell variable.
VLOOKUPCMT = yCell.Value
The VLOOKUPCMT function returns the value of the cell referenced by the yCell variable.
With Application.Caller
If Not .Comment Is Nothing Then
.Comment.Delete
Delete any existing comments from the formula cell.
If Not yCell.Comment Is Nothing Then
.AddComment yCell.Comment.Text
Copy the comment from the cell referenced by the yCell variable to the cell containing the formula.

Read More: How to Copy Comments in Excel

Things to Remember

You may need to recalculate every time you search for a new value. Otherwise, you may get erroneous results.

Download the Practice Workbook

Copying Comments.xlsm

<< Go Back to Comments in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Sub PullPicturesFromComments() Dim Comment As Comment Dim Range As Range Dim Visible As Boolean Dim ShapeHeight, ShapeWidth As Integer Application.ScreenUpdating = False For Each Comment In ActiveSheet.Comments With Comment ShapeHeight = .Shape.Height ShapeWidth = .Shape.Width Visible = .Visible .Visible = True On Error Resume Next Set Range = .Parent.Offset(0, 1) .Shape.CopyPicture Appearance:=xlScreen, Format:=xlPicture Range.PasteSpecial Selection.ShapeRange.LockAspectRatio = msoFalse Selection.Width = Range.Width Selection.Height = Range.Height .Visible = Visible End With Next Comment Application.ScreenUpdating = True End Sub

Sub PullPicturesFromComments() Dim Comment As Comment Dim Range As Range Dim Visible As Boolean Dim str_com As String Dim ShapeHeight, ShapeWidth As Integer Application.ScreenUpdating = False For Each Comment In ActiveSheet.Comments With Comment str_com = .Text ShapeHeight = .Shape.Height ShapeWidth = .Shape.Width .Text Text:="" & Chr(10) & "" Visible = .Visible .Visible = True On Error Resume Next Set Range = .Parent.Offset(0, 4) .Shape.CopyPicture Appearance:=xlScreen, Format:=xlPicture Range.PasteSpecial Selection.ShapeRange.LockAspectRatio = msoFalse Selection.Width = Range.Width Selection.Height = Range.Height .Visible = Visible .Text Text:=str_com .Parent.Offset(0, 3) = str_com End With Next Comment Application.ScreenUpdating = True End Sub

Function VLOOKUPCMT_New(lookup_value As Variant, table_array As Range, col_index_num As Long, range_lookup As Long) As Variant Application.Volatile Dim xReturn As Variant Dim yCell As Range Dim commentThread As CommentThreaded xReturn = Application.Match(lookup_value, table_array.Columns(1), range_lookup) If IsError(xReturn) Then VLOOKUPCMT_New = "Not Found" Else Set yCell = table_array.Columns(col_index_num).Cells(1)(xReturn) VLOOKUPCMT_New = yCell.Value With Application.Caller ' Delete existing comment or threaded comment If Not .Comment Is Nothing Then .Comment.Delete If Not .CommentThreaded Is Nothing Then .CommentThreaded.Delete ' Add new threaded comment if exists If Not yCell.CommentThreaded Is Nothing Then Set commentThread = yCell.CommentThreaded .AddCommentThreaded commentThread.Text End If End With End If End Function

13 Comments

KJ Jul 15, 2022 at 12:20 PM

Salam Shamim
Is it possible to add code to pull the picture from the comment?

Md. Shamim Reza Jul 20, 2022 at 12:25 PM

Walaikum Assalam, KJ. Thanks for your query.

You can try the following code. The pictures will be copied to the next column.
Then, you can resize them and right-click to save them as pictures.

Alternatively, you can copy the worksheet or the workbook and save it as Web Page (*.htm, *.html).
Then, all of the pictures will be saved to a new folder (named after the file) in the same location as the saved file.

Per Gunnar Nilsen Dec 3, 2022 at 3:09 AM

Hello. What is the code for transferring images and text from the comment field?
- Reply
  Per Gunnar Nilsen Dec 8, 2022 at 1:47 AM
  
  Hello. Thanks for the great help here. Little change here.
  I wish that
  comment field with image and text is transferred to another cell as a comment field with the same image and text. 🙂
- Reply
  Tanjima Hossain Dec 5, 2022 at 3:17 PM
  Hello NILSEN,
  Hope you are doing well. Here, I have inserted a comment to cell B3 and in this comment, we have some text along with an image. So, using a VBA code I will show the process of extracting the image and texts in different cells.
  
  • Type the following code.
```
 Sub PullPicturesFromComments()
Dim Comment As Comment
Dim Range As Range
Dim Visible As Boolean
Dim str_com As String
Dim ShapeHeight, ShapeWidth As Integer
Application.ScreenUpdating = False
For Each Comment In ActiveSheet.Comments
With Comment
str_com = .Text
ShapeHeight = .Shape.Height
ShapeWidth = .Shape.Width
.Text Text:="" & Chr(10) & ""
Visible = .Visible
.Visible = True
On Error Resume Next
Set Range = .Parent.Offset(0, 4)
.Shape.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Range.PasteSpecial
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.Width = Range.Width
Selection.Height = Range.Height
.Visible = Visible
.Text Text:=str_com
.Parent.Offset(0, 3) = str_com
End With
Next Comment
Application.ScreenUpdating = True
End Sub 
```
  In the figure above, look at the highlighted portions which you may want to change. Set Range = .Parent.Offset(0, 4) will insert the image in a cell which is 4 columns right to the main cell B3 and .Parent.Offset(0, 3) will insert the texts in a cell which is 3 columns right to the main cell B3.
  
  • Press F5.
  Then, we transferred the texts and images from the comment into different cells.
  
  Best Regards,
  Tanjima Hossain
  ExcelDemy
Per Gunnar Nilsen Dec 8, 2022 at 1:49 AM

Hello. Thanks for the great help here. Little change here.
I wish that
comment field with image and text is transferred to another cell as a comment field with the same image and text. 🙂
Per Gunnar Nilsen Dec 8, 2022 at 2:05 AM

Hello. Tries again.
Has comment field with image and text in cell b3 sheet 1
must be transferred
to sheet2 cell d3 as a comment field with the same image and text.
Per Gunnar Nilsen Dec 8, 2022 at 3:18 AM

Hi need help code or formula here. Wishes.
Sheet1 (Search product) in cell B5.
If I write (Milk), I want the comment field with image and text from Sheet2 cell d5 to appear in Sheet1 cell C5.

Sheet2.
list
cell D5(milk) picture of milk in the comment field
cell D6 (bread) Picture of bread in the comment field
cell D7 (newspaper) Picture of a newspaper in the comment field
Per Gunnar Nilsen Dec 8, 2022 at 3:30 AM

forgot to mention that the comment field in sheet1 Cell b5, that it retrieves the same size of the comment field that is made in Sheet2.
Per Gunnar Nilsen Dec 8, 2022 at 3:56 AM

there is a formula i need in Sheet1 cell B4.
Today I have VLOOKUPCMT but it only retrieves text from the comment field.
Is there any other formula I can use to get a comment field with an image and text.
- Reply
  Tanjima Hossain Dec 11, 2022 at 1:30 PM
  
  Hello Nilsen,
  
  Sorry, this formula will not work for copying both a comment and a picture as a comment. You have to copy only the text strings if you want to paste them as comments. But if you want to copy the contents as cell value then try the previous code.
  Stay in touch with ExcelDemy.
  
  Thanks
  Tanjima

D.A. Jun 27, 2024 at 8:38 PM

I know this is a dated post, but is there way to make this function with the macro copying actual comments rather than notes in newer versions of Excel? The macro works great if you want to copy “Notes” but does not work with trying to copy the “Comments” functions.

Shamima Sultana Jun 29, 2024 at 8:36 PM

Hello D.A.

Here updated the VLOOKUPCMT function and named VLOOKUPCMT_New where handled threaded comments in newer versions of Excel.
Added deletion of existing threaded comments and handling of threaded comments
To ensure that the function can handle both old-style comments and new threaded comments. Use the following updated code:

Copy Comments Using UDF Function

This will copy the comments. To copy notes use the previous code.

Regards
ExcelDemy

Vlookup to Copy Comments in Excel (with a UDF)

Step 1 – Open a New Module to Insert the VBA Code

Step 2 – Write VBA Code to Create UDF

Step 3 – Apply the User-Defined Function to Copy Comments

Things to Remember

Related Articles

Leave a reply Cancel reply