I have a number of cells that have comments such as :
apples=25
organes=60
pears=12
I would select any cells that contains a comment and have the macro
count up the NUMBERS in the comment and show the total in that cell
Thanks
This macro will:
Select all the cells on the sheet that have comments
If there are numbers in the comments, it will add them up
Write the result of that addition into the cell.
The following are assumptions that can easily be modified:
1. The numeric values are all positive integers.
2. If there are no numeric values in the comment, the contents of the
cell will be cleared.
To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select
the macro by name, and <RUN>.
==========================================
Option Explicit
Sub AddUpComments()
'adds up integers in comments
'writes sum into cell
Dim c As Range, rg As Range
Dim sComment As String
Dim sTotal As Long
Dim re As Object, mc As Object, m As Object
Set rg = Cells.SpecialCells(xlCellTypeComments)
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\b\d+\b"
re.Global = True
For Each c In rg
sTotal = 0
sComment = c.Comment.Text
If re.test(sComment) = True Then
Set mc = re.Execute(sComment)
For Each m In mc
sTotal = sTotal + m
Next m
c.Value = sTotal
Else
c.ClearContents
End If
Next c
End Sub
=============================
