Menu

Macro to add numbers in a text string

Search this Thread

Prev

Previous Post

Next Post

Next

#7

Report Post

Posted to microsoft.public.excel.misc

Ron Rosenfeld[_2_] Ron Rosenfeld[_2_] is offline

Ron Rosenfeld[_2_] is offline

external usenet poster

Posts: 1,045

Default

Macro to add numbers in a text string

On Fri, 5 Dec 2014 09:10:04 -0800 (PST), wrote:

Hi
I hope this someone can help with this

In column G I have a "Total" column Column I has a text string (Heading of "Result" as below

Total Result

25 Apples 10 Oranges 3 Grapes 12
15 Melons 10 Grapes 5
Oranges 8 Grapes 18

So basically sometimes the value is missing in the "Total" which is the sum of the numbers in the Results column. I would like some code to add the numbers in the result column and only add it to the total column if no value is entered. So the result would be

Total Result

25 Apples 10 Oranges 3 Grapes 12
15 Melons 10 Grapes 5
26 Oranges 8 Grapes 18

Many thanks for any help

Here's another macro:

==================================
Option Explicit
Sub TotalResults()
Dim R As Range, V As Variant
Dim RE As Object, MC As Object, M As Object
Const sPat As String = "\d+"
Dim I As Long, J As Long

With Worksheets("sheet1") 'Or ActiveSheet or any named worksheet
Set R = .Range("I1", .Cells(.Rows.Count, "I").End(xlUp)).Offset(columnoffset:=-2).Resize(columnsize:=3)
V = R
End With

Set RE = CreateObject("vbscript.regexp")
With RE
.Global = True
.Pattern = sPat
End With

For I = 2 To UBound(V)
If IsEmpty(V(I, 1)) And RE.test(V(I, 3)) = True Then
J = 0
Set MC = RE.Execute(V(I, 3))
For Each M In MC

J = J + M
Next M
V(I, 1) = J
End If
Next I

R = V

End Sub
============================

By the way, it would not be difficult to run this on ALL the rows, not just those with totals missing.

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Extract numbers from a string of text	Huber57	Excel Discussion (Misc queries)	0	May 27th 10 09:53 PM
Formula Text String: Formatting Text and Numbers?	dj479794	Excel Discussion (Misc queries)	5	June 30th 07 12:19 AM
extracting numbers within text string!	via135	Excel Worksheet Functions	6	May 5th 06 06:08 AM
EXTRACT NUMBERS FROM TEXT STRING	fiber_doc	Excel Worksheet Functions	4	November 28th 05 06:40 PM
Incorporating Numbers into a text string	Gary T	Excel Worksheet Functions	2	July 26th 05 11:09 AM

All times are GMT +1. The time now is 01:27 PM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement