Vlookup to Copy Comments in Excel (with a UDF)

Get FREE Advanced Excel Exercises with Solutions!

The built-in VLOOKUP function in Excel can find and return values only. However, it is not possible to copy comments along with values using this function. Using a user-defined function (UDF) can be a way of doing this. Follow the article to learn how to do that.


Steps to Copy Comments in Excel Using User-Defined Vlookup Function

Assume you 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. Now you want to use a VLOOKUP formula to return a value associated with the lookup value along with comments if it contains any. But as we have said, the VLOOKUP function is not fit for this job, i.e. copying the comments. So, we will create a UDF that works with the same mechanism as the VLOOKUP function.

Copy Comments in Excel Using User-Defined Vlookup Function

Follow the steps below to be able to do that.


๐Ÿ“Œ Step 1: Open a New Module to Insert VBA Code

  • First, press ALT+F11 to open the VBA window. Then select Insert >> New Module to create a new module as shown in the following picture.


๐Ÿ“Œ Step 2: Write VBA Code to Create UDF

  • After that, 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
  • Next, paste the copied code onto the blank module as shown in the picture below.

Copy Comments in Excel Using User-Defined Vlookup Function


๐Ÿ“Œ Step 3: Apply the User-Defined Function to Copy Comments

  • Now, enter the lookup value (3) in cell E5.
  • Finally, enter the following formula in cell F5. After that, you will get the desired result as follows. Here, the VLOOKUPCMT function works the same as 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 lookup for a new value. Otherwise, you may get erroneous results.


Download Practice Workbook

You can download the practice workbook from the download button below.


Conclusion

Now you know how to copy comments in Excel using the VLOOKUP formula. Hopefully, this has solved your problem. Use the comment section below for further queries or suggestions.ย Stay with us and keep learning.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

11 Comments
  1. Salam Shamim
    Is it possible to add code to pull the picture from the comment?

    • 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.

      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

      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.

  2. Reply
    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. ๐Ÿ™‚

    • 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.

      1

      โ€ข 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 

      2

      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.

      3

      Best Regards,
      Tanjima Hossain
      ExcelDemy

  3. Reply
    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. ๐Ÿ™‚

  4. Reply
    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.

  5. Reply
    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

  6. Reply
    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.

  7. Reply
    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.

    • 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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo