Enhancements to sub which writes cell value to comments
 

Need help for 2 modifications to the sub below

Within the selected range:
1. Empty cells or cells with formulas evaluating to "" (zero length null
strings) should not have any comments inserted.
2. For cells with existing comments prior to the sub's run, the cell results
are to be written as a new line below the text within the existing comments.
As-is the sub overwrites such cells with new comments.

Sub FormulaResultToComment()
'Joel .prog
For Each Cell In Selection
If Len(Cell.Formula) 0 Then
If Cell.Comment Is Nothing Then
Cell.AddComment
End If
'Cell.Comment.Text Text:=Cell.Formula
Cell.Comment.Text Text:=Cell.Value
End If
Next Cell
End Sub

Thanks
Max

Enhancements to sub which writes cell value to comments
 

see if this helps at all

Sub FormulaResultToComment()
'Joel .prog
Dim cell As Range
Dim cmt As String
For Each cell In Selection
If cell.Value 0 Or Len(cell.Address) = 0 Then
If cell.Comment Is Nothing Then
cell.AddComment "This is a test"
End If
cmt = cell.Comment.Text
cmt = cell.Comment.Text & vbCrLf & cell.Value
cell.Comment.Delete
cell.AddComment cmt
End If
Next cell
End Sub

--


Gary

"Max" wrote in message
...
Need help for 2 modifications to the sub below

Within the selected range:
1. Empty cells or cells with formulas evaluating to "" (zero length null
strings) should not have any comments inserted.
2. For cells with existing comments prior to the sub's run, the cell
results are to be written as a new line below the text within the existing
comments. As-is the sub overwrites such cells with new comments.

Sub FormulaResultToComment()
'Joel .prog
For Each Cell In Selection
If Len(Cell.Formula) 0 Then
If Cell.Comment Is Nothing Then
Cell.AddComment
End If
'Cell.Comment.Text Text:=Cell.Formula
Cell.Comment.Text Text:=Cell.Value
End If
Next Cell
End Sub

Thanks
Max

Enhancements to sub which writes cell value to comments
 

Gary, thanks. Tried out your revision.

Within the selected range:
1. Empty cells or cells with formulas evaluating to "" (zero length null
strings) should not have any comments inserted.

a. Empty cells - ok. No comments are inserted.
b. Cells with formulas evaluating to "" (zero length null strings) - not ok.
It inserts a comment: "This is a test", instead of not inserting any
comment.

2. For cells with existing comments prior to the sub's run, the cell
results are to be written as a new line below the text within the
existing comments. As-is the sub overwrites such cells with new comments.

Item 2 is ok, but a new prob has surfaced. For cells w/o existing comments
which are not empty, the sub now writes the line: "This is a test" ahead of
the cell's value in the comment. The line: "This is a test" should not be
written.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Enhancements to sub which writes cell value to comments
 

max:

is this it?

Sub FormulaResultToComment()
'Joel .prog
Dim cell As Range
Dim cmt As String
For Each cell In Selection
If cell.Value 0 Or Len(cell.Address) = 0 Then
If cell.Comment Is Nothing Then
'do nothing
Else
cmt = cell.Comment.Text
cmt = cell.Comment.Text & Chr(10) & cell.Value
cell.Comment.Delete
cell.AddComment cmt
End If
End If
Next cell
End Sub


--


Gary


"Max" wrote in message
...
Gary, thanks. Tried out your revision.

Within the selected range:
1. Empty cells or cells with formulas evaluating to "" (zero length null
strings) should not have any comments inserted.

a. Empty cells - ok. No comments are inserted.
b. Cells with formulas evaluating to "" (zero length null strings) - not ok.
It inserts a comment: "This is a test", instead of not inserting any comment.

2. For cells with existing comments prior to the sub's run, the cell results
are to be written as a new line below the text within the existing comments.
As-is the sub overwrites such cells with new comments.

Item 2 is ok, but a new prob has surfaced. For cells w/o existing comments
which are not empty, the sub now writes the line: "This is a test" ahead of
the cell's value in the comment. The line: "This is a test" should not be
written.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Enhancements to sub which writes cell value to comments
 

is this it?

Afraid not, Gary. Now there's no comments written into the selected range,
other than to those cells within it which contain existing comments.
Appreciate further advise.

Rgds
Max

Enhancements to sub which writes cell value to comments
 

i thought that's what you wanted?
1. Empty cells or cells with formulas evaluating to "" (zero length null
strings) should not have any comments inserted
this works for me

2. For cells with existing comments prior to the sub's run, the cell results
are to be written as a new line below the text within the existing comments
this works for me

i don't see any criteria to add comments to any other cells.

please list all of your criteria.

--


Gary


"Max" wrote in message
...
is this it?

Afraid not, Gary. Now there's no comments written into the selected range,
other than to those cells within it which contain existing comments.
Appreciate further advise.

Rgds
Max

Enhancements to sub which writes cell value to comments
 

i don't see any criteria to add comments to any other cells.
please list all of your criteria.

The missing part is the retention of the core functionality of the original
sub, which was to write cell values to comments. I sought the 2
modifications to the original sub, but its core functionality should be
retained. This seemed lost along the way.

Using your latest revision, when I run the sub on a range w/o any existing
comments but with formulas returning values other than null strings, nothing
happens. Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Enhancements to sub which writes cell value to comments
 

here's another try:

cells with values 0 should now get a comment with the cell value
cells with values that have existing comments, should get the cell value
added to the end of the existing comment
cells that are blank or equate to 0 should not get comments.
let me know

Sub FormulaResultToComment()
'Joel .prog
Dim cell As Range
Dim cmt As String
For Each cell In Selection
If cell.Value = 0 Or Len(cell.Address) = 0 Then
'do nothing
ElseIf Not cell.Comment Is Nothing Then
cmt = cell.Comment.Text
cmt = cell.Comment.Text & Chr(10) & cell.Value
cell.Comment.Delete
cell.AddComment cmt
Else
cmt = cell.Value
cell.AddComment cmt
End If
Next cell
End Sub


--


Gary

"Max" wrote in message
...
i don't see any criteria to add comments to any other cells.
please list all of your criteria.

The missing part is the retention of the core functionality of the
original sub, which was to write cell values to comments. I sought the 2
modifications to the original sub, but its core functionality should be
retained. This seemed lost along the way.

Using your latest revision, when I run the sub on a range w/o any existing
comments but with formulas returning values other than null strings,
nothing happens. Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Enhancements to sub which writes cell value to comments
 

First, many thanks for your patience, Gary.

Tried your latest rendition. It works 99% fine, great! The 1% off is that
for cells with formulas evaluating to "" (zero length null strings), there
were blank comments inserted. If it's difficult to get this last 1% settled,
think I could live with it. Appreciate your thoughts. Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Enhancements to sub which writes cell value to comments
 

maybe this?
Sub FormulaResultToComment()
'Joel .prog
Dim cell As Range
Dim cmt As String
For Each cell In Selection
If cell.Value = 0 Or Len(cell.Address) = 0 Or cell.Value = "" Then
'do nothing
ElseIf Not cell.Comment Is Nothing Then
cmt = cell.Comment.Text
cmt = cell.Comment.Text & Chr(10) & cell.Value
cell.Comment.Delete
cell.AddComment cmt
Else
cmt = cell.Value
cell.AddComment cmt
End If
Next cell
End Sub


--


Gary


"Max" wrote in message
...
First, many thanks for your patience, Gary.

Tried your latest rendition. It works 99% fine, great! The 1% off is that for
cells with formulas evaluating to "" (zero length null strings), there were
blank comments inserted. If it's difficult to get this last 1% settled, think
I could live with it. Appreciate your thoughts. Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Enhancements to sub which writes cell value to comments
 

maybe this?
Yessss! That did it well. Super!
Thanks, Gary.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---