add values before semicolon

**a.hudrea** · 11-06-2014, 02:45 AM

test.xlsx
I have attached an example of a desired result. And as the title says I want to sum the values that are before a semicolon. In some cells there could be only one value in another cell there could be up to three. Any advice would be really appreciated. Thank you very much.

**MickG** · 11-06-2014, 07:05 AM

Try this on Your basic data (as shown in your attached file)
NB:- This code will alter that data.

Sub MG06Nov17
Dim Rng As Range, Dn As Range, ac As Long
Dim Sp As Variant, n As Long
Dim Sp2 As Variant, num As Double
Set Rng = Range(Range("B3"), Range("B" & Rows.Count).End(xlUp))
For ac = 1 To 3
For Each Dn In Rng.Offset(, ac)
    If Not Dn = "" Then
        Sp = Split(Dn, ";")
        For n = 0 To UBound(Sp)
            Sp2 = Split(Sp(n), " ")
            If Val(Sp2(UBound(Sp2))) > 0 Then
                num = num + Val(Sp2(UBound(Sp2)))
            End If
        Next n
            Dn.Value = num: num = 0
    End If
Next Dn
Next ac
End Sub

Regards Mick

**apo** · 11-07-2014, 05:26 AM

Here's another...

Private Sub CommandButton1_Click()
    Dim n, i As Long, ii As Long, x, y
    With CreateObject("vbscript.regexp")
        .MultiLine = True
        .Global = True
        .IgnoreCase = False
        .Pattern = "(\s\d+;)"

        x = Range("B2:E" & Range("B" & Rows.Count).End(xlUp).Row).Value
        ReDim y(2 To UBound(x), 1 To 4)
        For i = 2 To UBound(x)
            For ii = 2 To 4
                Set myMatches = .Execute(x(i, ii))
                For Each n In myMatches
                    y(i, ii) = Val(y(i, ii)) + Val(Trim(Split(n, ";")(0)))
                Next n
            Next ii
        Next i
    End With
    Range("H3").Resize(UBound(y, 1) - 1, UBound(y, 2)).Value = y
    Range("H2").Resize(1, 4).Value = Application.Index(x, 1, 0)
    Range("H2").Resize(UBound(x)).Value = Application.Index(x, 0, 1)
End Sub