Adding Numbers with Text in Cell

[PhancyK]

So I'm trying to add a row of cells that contain both letters and numbers. I'm using the following macros:

Function add_num(cell1, ParamArray Arr() As Variant)

Dim temp As Double
For i = LBound(Arr) To UBound(Arr)
temp = temp + GetNumber(Arr(i))
Next

add_num = GetNumber(cell1.Value) + temp
End Function

Function GetNumber(ByVal str As String) As Double
Dim objRegEx As Object
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.IgnoreCase = True
objRegEx.Global = True

objRegEx.Pattern = "\d{1,2}([\.,][\d{1,2}])?"

Set allMatches = objRegEx.Execute(str)

For i = 0 To allMatches.Count - 1
result = result & allMatches.Item(i)
Next

GetNumber = result
End Function

And in the cell I want to display the value I have:
=add_num(H8,I8,J8,K8)

That works fine until I have multiple numbers in 1 cell, such as N12ES1. Which it interprets as 121 instead of adding 12+1.

Any ideas?

 

[Scott Huish]

Try this.
Replace all your code with this:

Function GetNumbers(r As String)
Dim m As Object, t
With CreateObject("vbscript.regexp")
    .Pattern = "\d+"
    .Global = True
    If .test(r) Then
        Set m = .Execute(r)
        For Each t In m: GetNumbers = GetNumbers + Val(t): Next
    End If
End With
End Function

Function AddNums(ParamArray Arr() As Variant)
Dim t
For Each t In Arr: AddNums = AddNums + GetNumbers(CStr(t)): Next
End Function

Excel 2010
	A	B
1	N12ES1	42
2	14
3	A15
Sheet1

Cell Formulas
Range	Formula
B1	=addnums(A1,A2,A3)

 

[Rick Rothstein]

Here is a way to do this without using Regular Expressions...

Function AddNums(ParamArray NumOrRng())
  Dim V As Variant, VV As Variant
  For Each V In NumOrRng
    If VarType(V) < vbArray Then
      AddNums = AddNums + GetNumSum(V)
    Else
      For Each VV In V
        AddNums = AddNums + GetNumSum(VV)
      Next
    End If
  Next
End Function

Function GetNumSum(ByVal S As String) As Long
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
  Next
  GetNumSum = Evaluate(Replace(Trim(S), " ", "+"))
End Function

Note: The argument to the AddNums function (a comma delimited text string) can be one or more single numerical constants, one or more single range references, one or more multi-cell range references or any combination of those in any order.

 

[PhancyK]

Thanks for your help, it worked perfectly.