VBA Function Split Sum School

[Stephen_IV]

I am looking to modify my code below. I need to update my function to be able to lookup a school and get the sum. For instance, Jefferson and get 167 or Washington and get 241. Thanks in advance.

VBA Sum Parenthesis Range.xlsm
	A
1	Jefferson(100), Madison(25)
2	Washington(230), Jackson(23), Lincoln(243)
3	Beardsley(35), Jefferson(24)
4	Cleveland(223)
5	Lincoln(9), Washington(11), Jefferson(43)
Sheet2

Function SumP(r As Range) As Variant
Dim i As Long
Dim Parts() As String
For Each xcell In r
Parts = Split(Replace(xcell, ")", "("), "(")
For i = 1 To UBound(Parts)
SumP = SumP + Val(Parts(i))
Next
Next
End Function

 

[Phuoc]

Function SumP(r As Range) As Variant
Application.Volatile
Dim i As Long, xCell As Range
Dim Parts() As String
For Each xCell In r
Parts = Split(Replace(xCell, ")", "("), "(")
For i = 1 To UBound(Parts)
SumP = SumP + Val(Parts(i))
Next
Next
End Function

 

[Stephen_IV]

Function SumP(r As Range) As Variant
Application.Volatile
Dim i As Long, xCell As Range
Dim Parts() As String
For Each xCell In r
Parts = Split(Replace(xCell, ")", "("), "(")
For i = 1 To UBound(Parts)
SumP = SumP + Val(Parts(i))
Next
Next
End Function

Thanks for the response but I need another parameter in the function like

Function SumP(r As Range, Sch as string) As Variant

so that I could sum the values of the school that I select.

 

[Phuoc]

Try again

Function SumP(r As Range, Sch As String) As Long
Application.Volatile
Dim i As Long, xCell As Range
Dim Parts() As String
For Each xCell In r
    Parts = Split(Replace(Replace(xCell, ",", ""), ")", "("), "(")
    For i = LBound(Parts) To UBound(Parts) Step 2
        If Trim(Parts(i)) = Sch Then SumP = SumP + Val(Parts(i + 1))
    Next i
Next
End Function

 

[Stephen_IV]

Okay it works great! Thank you so much for your help!

 

[Phuoc]

You're welcome!

 

[Rick Rothstein]

Here is more compact, single-loop function that you can also consider...

Function SumP(Rng As Range, Sch As String) As Long
  Dim V As Variant
  For Each V In Split(LCase(Join(Application.Transpose(Rng))), Sch & "(")
    SumP = SumP + Val(V)
  Next
End Function

 

[Peter_SSs]

Do you need to use vba or could you use standard worksheet functions?
Depending on your Excel version** you may need to confirm the formula with Ctrl+Shift+Enter, not just Enter.

** I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Stephen_IV.xlsm
	A	B	C	D
1	Jefferson(100), Madison(25)		Jefferson	167
2	Washington(230), Jackson(23), Lincoln(243)		Washington	241
3	Beardsley(35), Jefferson(24)
4	Cleveland(223)
5	Lincoln(9), Washington(11), Jefferson(43)
Sheet1

Cell Formulas
Range	Formula
D1:D2	D1	=SUM(--LEFT(SUBSTITUTE(REPLACE(", "&A$1:A$5&", "&C1&"(0",1,SEARCH(", "&C1&"(",", "&A$1:A$5&", "&C1&"(")+LEN(C1)+2,0),")",REPT(" ",20)),20))

 

[Stephen_IV]

Thanks to everyone who applied to this post! I have learned a ton!