How to Get Comment by Vlookup Function :

[Rajan_Verma]

if you want to Get Comment with Lookup Value in Result Cells , You can use this 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