Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to add up values in COMMENTs
Hi
I did receive an very nice macro to carryout a search of ALL comments on a worksheet. What I really wanted was to have a macro that would provide a total for an active cell. IE If I put my cursor on a12, and A12 contains a comment such as: apples=12 oranges=23 The macro would place the value of 35(12+23) in that cell. Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to add up values in COMMENTs
Do the texts in the comments follow explicit rules, i.e., is it that
you will always want to add all the numbers from the comment that follow '=' in the comment? Or will you always want to add all the numbers that are in the comment text at the end of each row within the comment? You might be up for a struggle as it's not that likely that you'd have such an explicit/enforced rules around the text in the comment. On Jun 30, 8:14*pm, pcor wrote: Hi I did receive an very nice macro to carryout a search of ALL comments on a worksheet. What I really wanted was to have a macro that would provide a total for an active cell. IE If I put my cursor on a12, and A12 contains a comment such as: apples=12 oranges=23 The macro would place the value of 35(12+23) in that cell. Thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to add up values in COMMENTs
On Jul 1, 7:38*am, AB wrote:
Do the texts in the comments follow explicit rules, i.e., is it that you will always want to add all the numbers from the comment that follow '=' in the comment? Or will you always want to add all the numbers that are in the comment text at the end of each row within the comment? You might be up for a struggle as it's not that likely that you'd have such an explicit/enforced rules around the text in the comment. On Jun 30, 8:14*pm, pcor wrote: Hi I did receive an very nice macro to carryout a search of ALL comments on a worksheet. What I really wanted was to have a macro that would provide a total for an active cell. IE If I put my cursor on a12, and A12 contains a comment such as: apples=12 oranges=23 The macro would place the value of 35(12+23) in that cell. Thanks in advance- Hide quoted text - - Show quoted text - Yes the number in the commenst will always be preceeded by a = sign such as apples=23 oranges=12 pears=3 Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to add up values in COMMENTs
On Wed, 30 Jun 2010 12:14:30 -0700 (PDT), pcor
wrote: Hi I did receive an very nice macro to carryout a search of ALL comments on a worksheet. What I really wanted was to have a macro that would provide a total for an active cell. IE If I put my cursor on a12, and A12 contains a comment such as: apples=12 oranges=23 The macro would place the value of 35(12+23) in that cell. Thanks in advance It's helpful if you keep your topic in a single thread. But all you need to do is modify the macro so it only looks at the active cell. For example, you could change this line: Set rg = Cells.SpecialCells(xlCellTypeComments) to Set rg = ActiveCell and you're done. Or you could do a little more rewriting to make it more compact. You did not indicate what you want to have done if you run the macro but ActiveCell has no comment. In this modification, I give you the opportunity to make a choice. As before, this adds up only positive integer values. If you may have decimal or negative values, it would require merely a change in re.pattern. ================================ Option Explicit Sub AddUpComments() 'adds up integers in comments 'in active cell Dim sComment As String Dim sTotal As Long Dim re As Object, mc As Object, m As Object Set re = CreateObject("vbscript.regexp") re.Pattern = "\b\d+\b" re.Global = True With ActiveCell sTotal = 0 On Error GoTo NoComment sComment = .Comment.Text On Error GoTo 0 If re.test(sComment) = True Then Set mc = re.Execute(sComment) For Each m In mc sTotal = sTotal + m Next m .Value = sTotal Else .ClearContents End If End With Exit Sub ======================== |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to add up values in COMMENTs
On Thu, 01 Jul 2010 23:11:20 -0400, Ron Rosenfeld
wrote: On Wed, 30 Jun 2010 12:14:30 -0700 (PDT), pcor wrote: Hi I did receive an very nice macro to carryout a search of ALL comments on a worksheet. What I really wanted was to have a macro that would provide a total for an active cell. IE If I put my cursor on a12, and A12 contains a comment such as: apples=12 oranges=23 The macro would place the value of 35(12+23) in that cell. Thanks in advance It's helpful if you keep your topic in a single thread. But all you need to do is modify the macro so it only looks at the active cell. For example, you could change this line: Set rg = Cells.SpecialCells(xlCellTypeComments) to Set rg = ActiveCell and you're done. Or you could do a little more rewriting to make it more compact. You did not indicate what you want to have done if you run the macro but ActiveCell has no comment. In this modification, I give you the opportunity to make a choice. As before, this adds up only positive integer values. If you may have decimal or negative values, it would require merely a change in re.pattern. ================================ Option Explicit Sub AddUpComments() 'adds up integers in comments 'in active cell Dim sComment As String Dim sTotal As Long Dim re As Object, mc As Object, m As Object Set re = CreateObject("vbscript.regexp") re.Pattern = "\b\d+\b" re.Global = True With ActiveCell sTotal = 0 On Error GoTo NoComment sComment = .Comment.Text On Error GoTo 0 If re.test(sComment) = True Then Set mc = re.Execute(sComment) For Each m In mc sTotal = sTotal + m Next m .Value = sTotal Else .ClearContents End If End With Exit Sub ======================== Oops, I left out the last few lines of the sub. Let's try again: ============================ Option Explicit Sub AddUpComments() 'adds up integers in comments 'in active cell Dim sComment As String Dim sTotal As Long Dim re As Object, mc As Object, m As Object Set re = CreateObject("vbscript.regexp") re.Pattern = "\b\d+\b" re.Global = True With ActiveCell sTotal = 0 On Error GoTo NoComment sComment = .Comment.Text On Error GoTo 0 If re.test(sComment) = True Then Set mc = re.Execute(sComment) For Each m In mc sTotal = sTotal + m Next m .Value = sTotal Else .ClearContents End If End With Exit Sub NoComment: If MsgBox("No Comment in Cell!" & vbLf & _ "Clear Cell Contents?", vbYesNo) = vbYes Then ActiveCell.ClearContents End If End Sub =================================== |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to add comments from cell values | Excel Programming | |||
Pulling out comments with macro | Excel Discussion (Misc queries) | |||
Macro to Add Comments with vlookup | Excel Programming | |||
Extract Comments and paste as values | Excel Discussion (Misc queries) | |||
Can Comments be automatically converted to text cell values? | Excel Discussion (Misc queries) |