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

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

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
---

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
---

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

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