Auto Insert of Comments for Cell

  • Thread starter Thread starter Buck Turgidson
  • Start date Start date
B

Buck Turgidson

I have a spreadsheet with a column containing about 500 rows.

For each row, I want to insert a comment. The comment text is in a column
of 500 rows in another spreadsheet.

Is there a way to automate the insertion of the comments from the source
column to the target column?

Thanks.
 
Hi

Try this with the comments text in column A of sheet2

Sub test()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Sheets("Sheet1").Range("A1:A500")
On Error Resume Next
cell.AddComment Text:=Sheets("Sheet2") _
.Range(cell.Address).Value
On Error GoTo 0
Next
Application.ScreenUpdating = True
End Sub
 
Try this with the comments text in column A of sheet2
Sub test()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Sheets("Sheet1").Range("A1:A500")
On Error Resume Next
cell.AddComment Text:=Sheets("Sheet2") _
.Range(cell.Address).Value
On Error GoTo 0
Next
Application.ScreenUpdating = True
End Sub
Click to expand...

Thanks. Worked great. Once small glitch was when the comment was a numeric
value with no text, it did not work. But I just edited the column and
placed a quote before the number before running the macro, which took care
of it.

Thanks again!
 
Hi Buck,
A of modification to Ron's will solve that
1) Use .text instead of .value

Additional comments:
2) Actually you didn't ask but if you want to replace a comment
either delete all comments in the range
or comment if you have a replacement.
otherwise old comment remains unchanged

3) you can use ActiveSheet if one of them really
is the activesheet.

Sub test111()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Sheets("Sheet1").Range("A1:A500")
On Error Resume Next
'Sheets("Sheet2").ClearComments '-- would be faster
If Sheets("sheettwo").Range(cell.Address).Text <> "" Then
cell.Comment.Delete '-- delete a possible preexisting comment
cell.AddComment Text:=Sheets("Sheet2") _
.Range(cell.Address).Text
End If
On Error GoTo 0
Next cell
Application.ScreenUpdating = True
End Sub

My page on cell comments is :
http://www.mvps.org/dmcritchie/excel/ccomment.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
Hi Buck,
A of modification to Ron's will solve that
1) Use .text instead of .value

Additional comments:
2) Actually you didn't ask but if you want to replace a comment
either delete all comments in the range
or comment if you have a replacement.
otherwise old comment remains unchanged

3) you can use ActiveSheet if one of them really
is the activesheet.
Click to expand...

Correction to my previous code although the line to clear the
range was commented out it was in the wrong place and would
have cleared comments from the wrong sheet -- though you
probably wold not have had comments in sheet2.

So rather than just suggesting
Sheets("Sheet1").Range("A1:A500").ClearComments '-- would be faster

it will be used as follows:

Sub test111()
'-- Sheet1 is receiving data from sheet2 for comments in sheet1
Dim cell As Range
Application.ScreenUpdating = False
Sheets("Sheet1").Range("A1:A500").ClearComments '-- would be faster
For Each cell In Sheets("Sheet1").Range("A1:A500")
On Error Resume Next
If Sheets("sheet2").Range(cell.Address).Text <> "" Then
' cell.Comment.Delete '-- delete a possible preexisting comment
cell.AddComment Text:=Sheets("Sheet2") _
.Range(cell.Address).Text
End If
On Error GoTo 0
Next cell
Application.ScreenUpdating = True
End Sub



David McRitchie said:
Hi Buck,
A of modification to Ron's will solve that
1) Use .text instead of .value

Additional comments:
2) Actually you didn't ask but if you want to replace a comment
either delete all comments in the range
or comment if you have a replacement.
otherwise old comment remains unchanged

3) you can use ActiveSheet if one of them really
is the activesheet.

Sub test111()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Sheets("Sheet1").Range("A1:A500")
On Error Resume Next
'Sheets("Sheet2").ClearComments '-- would be faster
If Sheets("sheettwo").Range(cell.Address).Text <> "" Then
cell.Comment.Delete '-- delete a possible preexisting comment
cell.AddComment Text:=Sheets("Sheet2") _
.Range(cell.Address).Text
End If
On Error GoTo 0
Next cell
Application.ScreenUpdating = True
End Sub

My page on cell comments is :
http://www.mvps.org/dmcritchie/excel/ccomment.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Buck Turgidson said:
Thanks. Worked great. Once small glitch was when the comment was a numeric
value with no text, it did not work. But I just edited the column and
placed a quote before the number before running the macro, which took care
of it.

Thanks again!
Click to expand...
Click to expand...
 
Hi buck

The master give you already a answer i see
See also the link to Debra's site on David's page
 
Back
Top