Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to count numbers in COMMENTS
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to count numbers in COMMENTS
On Tue, 29 Jun 2010 11:18:36 -0700 (PDT), pcor
wrote: 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 ============================= |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count comments funtion | Excel Programming | |||
count comments | Excel Discussion (Misc queries) | |||
Is there a way to extract numbers from comments for use in a formu | Excel Discussion (Misc queries) | |||
How do i count numbers and letters to find a total count of all | Excel Worksheet Functions | |||
Count Comments | Excel Worksheet Functions |