ExcelBanter - Vlookup

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Vlookup (https://www.excelbanter.com/excel-programming/391233-vlookup.html)

Is it possible to copy the comment on the cell together with the information
when executing a vlookup?

thanks.

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)

NPR_BRZ wrote:

Is it possible to copy the comment on the cell together with the information
when executing a vlookup?

thanks.

--

Dave Peterson

Hi Dave,
I am getting a #REF error:
the data (I placed comments on column G to test the code):
NPR001 #REF! NPR001 WK003
NPR002 NPR002 WK004
NPR003 NPR003 WK005
NPR004 NPR004 WK006
NPR005 NPR005 WK007
NPR006 NPR006 WK008
NPR007 NPR007 WK009
NPR008 #REF! NPR008 WK010
NPR009 NPR009 WK011
NPR010 NPR010 WK012
NPR011 NPR011 WK013
NPR012 NPR012 WK014
NPR013 NPR013 WK015
NPR014 NPR014 WK016
NPR015 NPR015 WK017
NPR016 NPR016 WK018

the command: =VlookupComment(B2,F:G, 2, FALSE)

the code, I copied and pasted from the message you posted.
Thanks a lot.
NPR
"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)

NPR_BRZ wrote:

Is it possible to copy the comment on the cell together with the information
when executing a vlookup?

thanks.

--

Dave Peterson

I couldn't duplicate your problem.

Are you sure you didn't put the formula in the wrong cell, then delete a row???

NPR_BRZ wrote:

Hi Dave,
I am getting a #REF error:
the data (I placed comments on column G to test the code):
NPR001 #REF! NPR001 WK003
NPR002 NPR002 WK004
NPR003 NPR003 WK005
NPR004 NPR004 WK006
NPR005 NPR005 WK007
NPR006 NPR006 WK008
NPR007 NPR007 WK009
NPR008 #REF! NPR008 WK010
NPR009 NPR009 WK011
NPR010 NPR010 WK012
NPR011 NPR011 WK013
NPR012 NPR012 WK014
NPR013 NPR013 WK015
NPR014 NPR014 WK016
NPR015 NPR015 WK017
NPR016 NPR016 WK018

the command: =VlookupComment(B2,F:G, 2, FALSE)

the code, I copied and pasted from the message you posted.
Thanks a lot.
NPR
"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)

NPR_BRZ wrote:

Is it possible to copy the comment on the cell together with the information
when executing a vlookup?

thanks.

--

Dave Peterson

--

Dave Peterson

Yes I am sure. The lines on the worksheet are exact the same you see below.

"Dave Peterson" wrote:

I couldn't duplicate your problem.

Are you sure you didn't put the formula in the wrong cell, then delete a row???

NPR_BRZ wrote:

Hi Dave,
I am getting a #REF error:
the data (I placed comments on column G to test the code):
NPR001 #REF! NPR001 WK003
NPR002 NPR002 WK004
NPR003 NPR003 WK005
NPR004 NPR004 WK006
NPR005 NPR005 WK007
NPR006 NPR006 WK008
NPR007 NPR007 WK009
NPR008 #REF! NPR008 WK010
NPR009 NPR009 WK011
NPR010 NPR010 WK012
NPR011 NPR011 WK013
NPR012 NPR012 WK014
NPR013 NPR013 WK015
NPR014 NPR014 WK016
NPR015 NPR015 WK017
NPR016 NPR016 WK018

the command: =VlookupComment(B2,F:G, 2, FALSE)

the code, I copied and pasted from the message you posted.
Thanks a lot.
NPR
"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)

NPR_BRZ wrote:

Is it possible to copy the comment on the cell together with the information
when executing a vlookup?

thanks.

--

Dave Peterson

--

Dave Peterson

Sorry,

I still couldn't duplicate the problem.

Does it work in a brand new worksheet in a brand new workbook?

NPR_BRZ wrote:

Yes I am sure. The lines on the worksheet are exact the same you see below.

"Dave Peterson" wrote:

I couldn't duplicate your problem.

Are you sure you didn't put the formula in the wrong cell, then delete a row???

NPR_BRZ wrote:

Hi Dave,
I am getting a #REF error:
the data (I placed comments on column G to test the code):
NPR001 #REF! NPR001 WK003
NPR002 NPR002 WK004
NPR003 NPR003 WK005
NPR004 NPR004 WK006
NPR005 NPR005 WK007
NPR006 NPR006 WK008
NPR007 NPR007 WK009
NPR008 #REF! NPR008 WK010
NPR009 NPR009 WK011
NPR010 NPR010 WK012
NPR011 NPR011 WK013
NPR012 NPR012 WK014
NPR013 NPR013 WK015
NPR014 NPR014 WK016
NPR015 NPR015 WK017
NPR016 NPR016 WK018

the command: =VlookupComment(B2,F:G, 2, FALSE)

the code, I copied and pasted from the message you posted.
Thanks a lot.
NPR
"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)

NPR_BRZ wrote:

Is it possible to copy the comment on the cell together with the information
when executing a vlookup?

thanks.

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson