Vlookup to Copy Comments in Excel (with a UDF)

The built-in VLOOKUP function in excel can find and return values only. But, 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.

Table of Contents hide

Download Practice Workbook

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

📌 Step 1: Open a New Module to Insert VBA Code

📌 Step 2: Write VBA Code to Create UDF

📌 Step 3: Apply the User-Defined Function to Copy Comments

Things to Remember

Conclusion

Download Practice Workbook

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

Copying Comments.xlsm

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 which works with the same mechanism as the VLOOKUP function does.

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.

Similar Readings

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

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

Things to Remember

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

Conclusion

Now you know how to copy comments in excel using the VLOOKUP formula. Hopefully, this has solved your problem. Please use the comment section below for further queries or suggestions. Do visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.

2 Comments

KJ Jul 15, 2022 at 12:20 PM

Salam Shamim
Is it possible to add code to pull the picture from the comment?
- Reply
  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.
```
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.

Vlookup to Copy Comments in Excel (with a UDF)

Download Practice Workbook

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

📌 Step 1: Open a New Module to Insert VBA Code

📌 Step 2: Write VBA Code to Create UDF

📌 Step 3: Apply the User-Defined Function to Copy Comments

Things to Remember

Conclusion

Related Articles

Md. Shamim Reza

How to Superscript Graph in Excel (3 Quick Ways)

How to Change Font in Excel to All Caps (6 Simple Ways)

How to Make a Checklist in Excel (with Easy Steps)

How to Calculate Exponential in Excel (3 Easy Methods)

Leave a reply Cancel reply