I have a number of comments in some of my cells
Example :Sugar=200
salt=12
candy=400
Is there a way to add up all these number(200+12+400)
and place the number 612 in the cell where the comments are held?
Thanks

You can see from the lack of replies that this is not normally done,
certainly not by a formula. Comments are generally just that - comments about
the data in the cells.

However, I had a go writing a macro. I hope that you don't mind. I've just
got it working (Two comments). Press Alt + F11, Insert, Module and copy the
code into the Module. Close the VB editor and with the Active sheet having
comments Run the macro. Tools, Macro, Select the macro and click Run.

If this is on the lines that you want I'll improve the code, post back when
you have tried it. If there is anything in the cell this will be over
written.

Also you have to run the code each time a comment is updated. It is not a
function.

Best of luck
Peter

"pcor" wrote:

I have a number of comments in some of my cells
Example :Sugar=200
salt=12
candy=400
Is there a way to add up all these number(200+12+400)
and place the number 612 in the cell where the comments are held?
Thanks

I forgot the code

Sub Test()
Dim i As Integer
Dim myText As String, List, myTotal As Double
Dim cell As Range
Set cmt = ActiveSheet.Comments
Selection.SpecialCells(xlCellTypeComments).Select

For Each cell In Selection
cell.Select
myTotal = 0
myText = cell.Comment.Text
myText = WorksheetFunction.Substitute(myText, Chr(10), " ")
myText = WorksheetFunction.Substitute(myText, "=", " ")
myText = Trim(myText)
List = Split(myText, " ")
For i = LBound(List) To UBound(List)
If IsNumeric(List(i)) Then
myTotal = myTotal + List(i)
End If
Next i
cell = myTotal
Next cell
Cells(1, 1).Select
End Sub

I've tried to make a function for this without success. best of luck
Peter

"Billy Liddel" wrote:

You can see from the lack of replies that this is not normally done,
certainly not by a formula. Comments are generally just that - comments about
the data in the cells.

However, I had a go writing a macro. I hope that you don't mind. I've just
got it working (Two comments). Press Alt + F11, Insert, Module and copy the
code into the Module. Close the VB editor and with the Active sheet having
comments Run the macro. Tools, Macro, Select the macro and click Run.

If this is on the lines that you want I'll improve the code, post back when
you have tried it. If there is anything in the cell this will be over
written.

Also you have to run the code each time a comment is updated. It is not a
function.

Best of luck
Peter

"pcor" wrote:

I have a number of comments in some of my cells
Example :Sugar=200
salt=12
candy=400
Is there a way to add up all these number(200+12+400)
and place the number 612 in the cell where the comments are held?
Thanks

well here's the function

Function CommentTotal(d)
'Must force calulation with Ctrl + Alt + F9
Dim mytotal As Double, i As Integer
Application.Volatile
myText = d.Comment.Text
myText = WorksheetFunction.Substitute(myText, Chr(10), " ")
myText = WorksheetFunction.Substitute(myText, "=", " ")
myText = Trim(myText)
List = Split(myText, " ")
For i = LBound(List) To UBound(List)
If IsNumeric(List(i)) Then
mytotal = mytotal + List(i)
End If
Next i
CommentTotal = mytotal
End Function

Again, copy this into a VB Modue. If cell E4 contains a comment then in E4
type the formula =Commenttotal(e4)

You will not get a circular reference, because it is not operating on the
actual cell. However, if you add to a comment with a number the formula will
not automatically update or even if you press F9. To force calculation press
Ctrl + Alt + F9

I found this interesting to try, but are you sure that you want to go this
way? It is easier to update data placed in the worksheet.

Regards
Peter

No luck with the macro. It seem to be reading every comment on the sheet and
I am not sure where the answer end s up
BUT the function worked fine.
Can you tell me how to modify the macro so that it would perform like the
function (ie place the cursor in the cell where you want the answer and
activate the macro. In any case MANT THANKS


"Billy Liddel" wrote:

I forgot the code

Sub Test()
Dim i As Integer
Dim myText As String, List, myTotal As Double
Dim cell As Range
Set cmt = ActiveSheet.Comments
Selection.SpecialCells(xlCellTypeComments).Select

For Each cell In Selection
cell.Select
myTotal = 0
myText = cell.Comment.Text
myText = WorksheetFunction.Substitute(myText, Chr(10), " ")
myText = WorksheetFunction.Substitute(myText, "=", " ")
myText = Trim(myText)
List = Split(myText, " ")
For i = LBound(List) To UBound(List)
If IsNumeric(List(i)) Then
myTotal = myTotal + List(i)
End If
Next i
cell = myTotal
Next cell
Cells(1, 1).Select
End Sub

I've tried to make a function for this without success. best of luck
Peter

"Billy Liddel" wrote:

You can see from the lack of replies that this is not normally done,
certainly not by a formula. Comments are generally just that - comments about
the data in the cells.

However, I had a go writing a macro. I hope that you don't mind. I've just
got it working (Two comments). Press Alt + F11, Insert, Module and copy the
code into the Module. Close the VB editor and with the Active sheet having
comments Run the macro. Tools, Macro, Select the macro and click Run.

If this is on the lines that you want I'll improve the code, post back when
you have tried it. If there is anything in the cell this will be over
written.

Also you have to run the code each time a comment is updated. It is not a
function.

Best of luck
Peter

"pcor" wrote:

I have a number of comments in some of my cells
Example :Sugar=200
salt=12
candy=400
Is there a way to add up all these number(200+12+400)
and place the number 612 in the cell where the comments are held?
Thanks

Hi

Sorry about the macro, it works for me. It selects each cell with a comment
and examines the text for numberic data. If there are no numbers it will
place a zero in the comment cell

Are you sure that the comment is not obscuring the cell? I played around
with the data and the cells becames obsured making it difficult to read the
results. I'd be interested to hear further if this is the case otherwize I'm
not sure what is happening.

Regards
Peter

"pcor" wrote:

No luck with the macro. It seem to be reading every comment on the sheet and
I am not sure where the answer end s up
BUT the function worked fine.
Can you tell me how to modify the macro so that it would perform like the
function (ie place the cursor in the cell where you want the answer and
activate the macro. In any case MANT THANKS


"Billy Liddel" wrote:

I forgot the code

Sub Test()
Dim i As Integer
Dim myText As String, List, myTotal As Double
Dim cell As Range
Set cmt = ActiveSheet.Comments
Selection.SpecialCells(xlCellTypeComments).Select

For Each cell In Selection
cell.Select
myTotal = 0
myText = cell.Comment.Text
myText = WorksheetFunction.Substitute(myText, Chr(10), " ")
myText = WorksheetFunction.Substitute(myText, "=", " ")
myText = Trim(myText)
List = Split(myText, " ")
For i = LBound(List) To UBound(List)
If IsNumeric(List(i)) Then
myTotal = myTotal + List(i)
End If
Next i
cell = myTotal
Next cell
Cells(1, 1).Select
End Sub

I've tried to make a function for this without success. best of luck
Peter

"Billy Liddel" wrote:

You can see from the lack of replies that this is not normally done,
certainly not by a formula. Comments are generally just that - comments about
the data in the cells.

However, I had a go writing a macro. I hope that you don't mind. I've just
got it working (Two comments). Press Alt + F11, Insert, Module and copy the
code into the Module. Close the VB editor and with the Active sheet having
comments Run the macro. Tools, Macro, Select the macro and click Run.

If this is on the lines that you want I'll improve the code, post back when
you have tried it. If there is anything in the cell this will be over
written.

Also you have to run the code each time a comment is updated. It is not a
function.

Best of luck
Peter

"pcor" wrote:

I have a number of comments in some of my cells
Example :Sugar=200
salt=12
candy=400
Is there a way to add up all these number(200+12+400)
and place the number 612 in the cell where the comments are held?
Thanks

If you get the macro to work and want to restrict the cells it looks at,
create a range name that includes the cells with the numbers in the comments,
call it say MyRange. Then include the following lines

set rng = range("myrange")
rng.select

then the macro will only look at the cells in MyRange. I've done it for you
below

Sub Test()
Dim i As Integer
Dim myText As String, List, myTotal As Double
Dim cell As Range, rng as range
set rng = range("myrange")
rng.select
Set cmt = ActiveSheet.Comments
Selection.SpecialCells(xlCellTypeComments).Select

For Each cell In Selection
cell.Select
myTotal = 0
myText = cell.Comment.Text
myText = WorksheetFunction.Substitute(myText, Chr(10), " ")
myText = WorksheetFunction.Substitute(myText, "=", " ")
myText = Trim(myText)
List = Split(myText, " ")
For i = LBound(List) To UBound(List)
If IsNumeric(List(i)) Then
myTotal = myTotal + List(i)
End If
Next i
cell = myTotal
Next cell
Cells(1, 1).Select
End Sub


Regards
Peter