Vlookup Comments :

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