How to Get Comment by Vlookup Function :

if you want to Get Comment with Lookup Value in Result Cells , You can use this Code

Code:

---

Sub VlookupByCodes()

Dim ResultRange As Range
Dim SearchRange As Range
Dim ColNum As Integer
Dim LookUpValue As Range
Dim cellResult As Range
Dim CellSearch As Range
Dim i As Integer


        Set ResultRange = Application.InputBox("Select the Range Where You want Output", , , , , , , 8)
        Set LookUpValue = Application.InputBox("Select the Range of searchable Value", , , , , , , 8)
        Set SearchRange = Application.InputBox("Select the Range to Search Value", , , , , , , 8)
        ColNum = Application.InputBox("Give Colnum")
        i = 1
               
                    For Each cellResult In ResultRange
                            For Each CellSearch In SearchRange
                                    If CellSearch.Value = LookUpValue.Cells(i, 1).Value Then
                                        cellResult.Value = CellSearch.Offset(0, ColNum).Value
                                                If hasComment(CellSearch.Offset(0, ColNum)) = True Then
                                                    cellResult.AddComment
                                                    cellResult.Comment.Visible = True
                                                    cellResult.Comment.Text CellSearch.Offset(0, ColNum).Comment.Text
                                                End If
                                        i = i + 1
                                        Exit For
                                    End If
                            Next
                    Next
Set ResultRange = Nothing
Set LookUpValue = Nothing
Set SearchRange = Nothing
Set cellResult = Nothing
Set CellSearch = Nothing
       
End Sub

Private Function hasComment(cell As Range) As Boolean
On Error GoTo err:
If cell.Comment.Text <> "" Then
hasComment = True
Else
hasComment = False
End If
err:
If err.Number <> 0 Then
hasComment = False
End If
End Function

---