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