If you change your routine to use a cell in another column, you could use the
=vlookup() and your life may get better. I think it would be time worth
spending.

But if you can't...

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().

===================
If you decide that you want to use an adjacent cell (I'd do my best to use
this!), you could use a different function to retrieve those comments.

Also saved from a previous post:

You can retrieve the text from a comment with a userdefined function like:

Option Explicit
Function GetComment(FCell As Range) As Variant
Application.Volatile

Set FCell = FCell(1)

If FCell.Comment Is Nothing Then
GetComment = ""
Else
GetComment = FCell.Comment.Text
End If

End Function

Then you can use it like any other function:

=getcomment(a1)

But be aware that the function won't evaluate when you just change the comment.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

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


JRW wrote:

I receive a new file monthly that has some of the same part numbers in the
file but is not the same. I am currently using Vlookup to copy the data that
I need from the old file (last months) to the new file, however, comments
that were added to the cells the previous month aren't copied over using
vlookup but just the cell data. I need a means to also copy over the cells
comments from the last months file.

Any help would be appreciated.

--

Dave Peterson

This portion should have deleted any existing comment:

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

Are you sure it didn't work after the recalc?

But if you meant that if the function returned "not found", then the existing
comment was kept.

But you could delete that always:

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

JRW wrote:

Dave, thanks for the help. I tried both approaches and the VlookupComment
user-defined function worked well. The only enhancement that would be nice
is the situation where for example, I have ran the function and then found
that I had an error in the "myVal" cell and there was a comment that was
copied over. Anyway, after I fix the value in the "myVal" cell and press F9
to rerun the function, the original comment that was copied over is still
there (it's doesn't clear the comment). thanks, JRW

"Dave Peterson" wrote:

If you change your routine to use a cell in another column, you could use the
=vlookup() and your life may get better. I think it would be time worth
spending.

But if you can't...

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().

===================
If you decide that you want to use an adjacent cell (I'd do my best to use
this!), you could use a different function to retrieve those comments.

Also saved from a previous post:

You can retrieve the text from a comment with a userdefined function like:

Option Explicit
Function GetComment(FCell As Range) As Variant
Application.Volatile

Set FCell = FCell(1)

If FCell.Comment Is Nothing Then
GetComment = ""
Else
GetComment = FCell.Comment.Text
End If

End Function

Then you can use it like any other function:

=getcomment(a1)

But be aware that the function won't evaluate when you just change the comment.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

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


JRW wrote:

I receive a new file monthly that has some of the same part numbers in the
file but is not the same. I am currently using Vlookup to copy the data that
I need from the old file (last months) to the new file, however, comments
that were added to the cells the previous month aren't copied over using
vlookup but just the cell data. I need a means to also copy over the cells
comments from the last months file.

Any help would be appreciated.

--

Dave Peterson


--

Dave Peterson

Dave, thanks..... I relooked at it and indeed it is working okay. What was
really happening is that once the vlookupcomment function is performed and
there is the data and comment read, if say for example, I noticed I made an
error on the original worksheet that I am reading the data and comments from
and change the cell that I am using for the vlookup to a value that doesn't
exist on the new worksheet, the original comment ends up staying on the new
worksheet even after recalculation. So, in a nut shell, I had a part number
wrong on the worksheet that is my vlookup data (the part number is what I am
using as the reference column), then even though I correct the part number,
the comment from the original vlookupcomment calculation is still there on
the new worksheet. I hope this makes sense.... thanks, your help is
appreicated. JRW

"Dave Peterson" wrote:

This portion should have deleted any existing comment:

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

Are you sure it didn't work after the recalc?

But if you meant that if the function returned "not found", then the existing
comment was kept.

But you could delete that always:

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

JRW wrote:

Dave, thanks for the help. I tried both approaches and the VlookupComment
user-defined function worked well. The only enhancement that would be nice
is the situation where for example, I have ran the function and then found
that I had an error in the "myVal" cell and there was a comment that was
copied over. Anyway, after I fix the value in the "myVal" cell and press F9
to rerun the function, the original comment that was copied over is still
there (it's doesn't clear the comment). thanks, JRW

"Dave Peterson" wrote:

If you change your routine to use a cell in another column, you could use the
=vlookup() and your life may get better. I think it would be time worth
spending.

But if you can't...

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().

===================
If you decide that you want to use an adjacent cell (I'd do my best to use
this!), you could use a different function to retrieve those comments.

Also saved from a previous post:

You can retrieve the text from a comment with a userdefined function like:

Option Explicit
Function GetComment(FCell As Range) As Variant
Application.Volatile

Set FCell = FCell(1)

If FCell.Comment Is Nothing Then
GetComment = ""
Else
GetComment = FCell.Comment.Text
End If

End Function

Then you can use it like any other function:

=getcomment(a1)

But be aware that the function won't evaluate when you just change the comment.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

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


JRW wrote:

I receive a new file monthly that has some of the same part numbers in the
file but is not the same. I am currently using Vlookup to copy the data that
I need from the old file (last months) to the new file, however, comments
that were added to the cells the previous month aren't copied over using
vlookup but just the cell data. I need a means to also copy over the cells
comments from the last months file.

Any help would be appreciated.

--

Dave Peterson


--

Dave Peterson

Did the suggested change to the function make the problem go away?

JRW wrote:

Dave, thanks..... I relooked at it and indeed it is working okay. What was
really happening is that once the vlookupcomment function is performed and
there is the data and comment read, if say for example, I noticed I made an
error on the original worksheet that I am reading the data and comments from
and change the cell that I am using for the vlookup to a value that doesn't
exist on the new worksheet, the original comment ends up staying on the new
worksheet even after recalculation. So, in a nut shell, I had a part number
wrong on the worksheet that is my vlookup data (the part number is what I am
using as the reference column), then even though I correct the part number,
the comment from the original vlookupcomment calculation is still there on
the new worksheet. I hope this makes sense.... thanks, your help is
appreicated. JRW

"Dave Peterson" wrote:

This portion should have deleted any existing comment:

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

Are you sure it didn't work after the recalc?

But if you meant that if the function returned "not found", then the existing
comment was kept.

But you could delete that always:

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

JRW wrote:

Dave, thanks for the help. I tried both approaches and the VlookupComment
user-defined function worked well. The only enhancement that would be nice
is the situation where for example, I have ran the function and then found
that I had an error in the "myVal" cell and there was a comment that was
copied over. Anyway, after I fix the value in the "myVal" cell and press F9
to rerun the function, the original comment that was copied over is still
there (it's doesn't clear the comment). thanks, JRW

"Dave Peterson" wrote:

If you change your routine to use a cell in another column, you could use the
=vlookup() and your life may get better. I think it would be time worth
spending.

But if you can't...

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().

===================
If you decide that you want to use an adjacent cell (I'd do my best to use
this!), you could use a different function to retrieve those comments.

Also saved from a previous post:

You can retrieve the text from a comment with a userdefined function like:

Option Explicit
Function GetComment(FCell As Range) As Variant
Application.Volatile

Set FCell = FCell(1)

If FCell.Comment Is Nothing Then
GetComment = ""
Else
GetComment = FCell.Comment.Text
End If

End Function

Then you can use it like any other function:

=getcomment(a1)

But be aware that the function won't evaluate when you just change the comment.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

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


JRW wrote:

I receive a new file monthly that has some of the same part numbers in the
file but is not the same. I am currently using Vlookup to copy the data that
I need from the old file (last months) to the new file, however, comments
that were added to the cells the previous month aren't copied over using
vlookup but just the cell data. I need a means to also copy over the cells
comments from the last months file.

Any help would be appreciated.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson