Enhancements to sub which writes cell value to comments

[Max]

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

 

[Gary Keramidas]

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

 

[Max]

Gary, thanks. Tried out your revision.

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.

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.

---

 

[Gary Keramidas]

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

 

[Max]

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

 

[Gary Keramidas]

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.

 

[Max]

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.

---

 

[Gary Keramidas]

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

 

[Max]

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.

---

 

[Gary Keramidas]

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

 

[Max]

maybe this?
Yessss! That did it well. Super!
Thanks, Gary.

---