How to show original comment in cell when using Vlookup in Excel?

[Lucy]

Hi there,

There are 2 worksheets on the same workbook.

Sheet#1 consists of raw data
Sheet#2 consists of a Vlookup for looking up data from Sheet#1

I've added comments on several cells in Sheet#1
After I use Vlookup function on Sheet#2, only the cell value shows but NOT
the comments. How can I show the comments as well?

Thanks.

 

[Gord Dibben]

Comments are not brought over with this type of function, only the found value.

Bringing Comments over would require a copy/paste which would wipe out your
formulas.

Perhaps some type of event code to attach the Comment to the cell based upon a
value. but I'm not the one to write that.

 

[Dave Peterson]

Usually formulas in cells can only return values to those cells--not change
formats, not change other cells, ...

But comments are an exception (or bug that may be fixed???).

Saved from a previous post:

Not by anything built into excel--but you could use a User Defined Function.

Do you want to try a little macro?

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

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 .Comment Is Nothing Then
'do nothing
Else
.Comment.Delete
End If

If myLookupCell.Comment Is Nothing Then
'no comment, do nothing
Else
.AddComment Text:=myLookupCell.Comment.Text
End If
End With
End If

End Function

This kind of function could be one calculation behind. If the comment in the
table changes, then you'll want to force a recalculation before you believe the
results.

Application.volatile true
means that excel will recalculate each of these formulas each time excel
recalculates. You may notice a slowdown in your workbook.

If you remove this line, then the results in the cell will be ok, but the
comment may be wrong.

(The results in the cell should always be ok--it's the comment that's the
trouble.)

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, 'sheet 33'!a:e, 5, false)

It looks a lot like =vlookup().

 

[Lucy]

Hi Dave,

Thanks. I've used your code & it's very helpful!

Now, I just modified my formula. Instead of using Vlookup, now I use a
combination of Index/Small/Row because I want it to show multiple results
while looking up at a single value.

I'm trying to modify your code to see if I can show the comments as well.
But I'm unable to figure out the "res" part...Can you please give me some
ideas? Or maybe there are other easier ways to write a new macro?

formula in Excel:
=INDEX($A$1:$B$9,SMALL(IF($A$1:$A$9=$A$15,ROW($A$1:$A$9)),ROW(1:1)),2)

This is my code (which actually doesn't work):

Option Explicit
Function IndexComment(myArray As Range, myRow_Num As Integer, _
myColumn_Num As Integer) As Variant

Application.Volatile True

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

res = Application.Index(myArray, myRow_Num, myColumn_Num)
If IsError(res) Then
IndexComment = "Not Found"
Else
Set myLookupCell = myArray.Columns(myColumn_Num).Cells(1)(res)
IndexComment = myLookupCell.Value
With Application.Caller
If .Comment Is Nothing Then
'do nothing
Else
.Comment.Delete
End If

If myLookupCell.Comment Is Nothing Then
'no comment, do nothing
Else
.AddComment Text:=myLookupCell.Comment.Text
End If
End With
End If

End Function