Dear Experts,
When you do a vlookup from one worksheet to another and the target cell has
a comment attached to it is there a way to get the comment to appear in the
destination cell as well as the data?
regards
Martina

One of the "features/bugs" of excel is that you can return comments using a user
defined function.

Actually, you could use a UDF to retrieve the value and comment:

Option Explicit
Function VlookupComment(myVal As Variant, myTable As Range, _
myColumn As Long, myBoolean As Boolean) As Variant

Application.Volatile True

Dim res As Variant 'could be an error
Dim myLookupCell As Range

With Application.Caller
If .Comment Is Nothing Then
'do nothing
Else
.Comment.Delete
End If
end with

res = Application.Match(myVal, myTable.Columns(1), myBoolean)
If IsError(res) Then
VlookupComment = "Not Found"
Else
Set myLookupCell = myTable.Columns(myColumn).Cells(1)(res)
VlookupComment = myLookupCell.Value
With Application.Caller
If myLookupCell.Comment Is Nothing Then
'no comment, do nothing
Else
.AddComment Text:=myLookupCell.Comment.Text
End If
End With
End If

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=VlookupComment(a1, 'sheet2'!a:e, 5, false)

========
This line:
Application.Volatile True
tells excel to update the value and comment each time excel recalculates. The
reason is that changing a comment won't inform excel that anything needs to be
recalculated.

If you use lots of these formulas, expect a slowdown. If your comments never
change, you could remove that line of code.

Martina wrote:

Dear Experts,
When you do a vlookup from one worksheet to another and the target cell has
a comment attached to it is there a way to get the comment to appear in the
destination cell as well as the data?
regards
Martina

--

Dave Peterson

"Dave Peterson" wrote:

One of the "features/bugs" of excel is that you can return comments using a user
defined function.

Actually, you could use a UDF to retrieve the value and comment:

Option Explicit
Function VlookupComment(myVal As Variant, myTable As Range, _
myColumn As Long, myBoolean As Boolean) As Variant

Application.Volatile True

Dim res As Variant 'could be an error
Dim myLookupCell As Range

With Application.Caller
If .Comment Is Nothing Then
'do nothing
Else
.Comment.Delete
End If
end with

res = Application.Match(myVal, myTable.Columns(1), myBoolean)
If IsError(res) Then
VlookupComment = "Not Found"
Else
Set myLookupCell = myTable.Columns(myColumn).Cells(1)(res)
VlookupComment = myLookupCell.Value
With Application.Caller
If myLookupCell.Comment Is Nothing Then
'no comment, do nothing
Else
.AddComment Text:=myLookupCell.Comment.Text
End If
End With
End If

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=VlookupComment(a1, 'sheet2'!a:e, 5, false)

========
This line:
Application.Volatile True
tells excel to update the value and comment each time excel recalculates. The
reason is that changing a comment won't inform excel that anything needs to be
recalculated.

If you use lots of these formulas, expect a slowdown. If your comments never
change, you could remove that line of code.

Martina wrote:

Dear Experts,
When you do a vlookup from one worksheet to another and the target cell has
a comment attached to it is there a way to get the comment to appear in the
destination cell as well as the data?
regards
Martina

--

Dave Peterson


Thank you, for your trouble. I'll be sinking my teeth into that.
regards
Martina