Microsoft Excel - Total left to right with macro

Asked By John on 02-Jan-13 05:42 AM

 06/10=3 11/10=6 15/10=12 22/10=6 26/10=3 Total = 30 06/10=3 12/10=3 13/10=6 15/10=12 17/10=12 Total = 36 06/10=3 12/10=3 26/10=3 Total = 9 06/10=3 28/10=3 31/10=3 01/11=3 21/11=2

I want to total the the table as above. Eg. 3+6+12+6+3 =30. All column doesnot contain value. I want to total from left to right with macro.
Harry Boughen replied to John on 02-Jan-13 10:41 PM
Hello John,
I assume you want to split the strings at the equal sign and sum the values that result.  If so, try this.
=SUM(VALUE(RIGHT(A1:C1,LEN(A1:C1)-FIND("=",A1:C1))))
This has to be entered as an array formula (CTRL/SHIFT/ENTER) and will be enclosed in curly brackets.  Adjust the ranges to suit your data.  It wont work in its current form if there are blank cells in the range.  I haven't tried it with dynamic ranging yet.
Harry
wally eye replied to John on 07-Jan-13 01:20 PM

Put this in a new module:

Public Function RtEqTot(ByVal Target As Excel.Range) As Double

Dim arrInput        As Variant

Dim dblTot        As Double
Dim intPos        As Integer
Dim lngRow        As Long
Dim lngCol        As Long

If Target.Cells.Count > 0 Then
If Target.Cells.Count > 1 Then
arrInput = Target.Value
Else
ReDim arrInput(1 To 1, 1 To 1)
arrInput(1, 1) = Target.Value
End If
For lngRow = LBound(arrInput, 1) To UBound(arrInput, 1)
For lngCol = LBound(arrInput, 2) To UBound(arrInput, 2)
If arrInput(lngRow, lngCol) > "" Then
intPos = InStr(1, arrInput(lngRow, lngCol), "=")
If intPos > 0 Then
If IsNumeric(Mid(arrInput(lngRow, lngCol), intPos + 1)) Then
dblTot = dblTot + CDbl(Mid(arrInput(lngRow, lngCol), intPos + 1))
End If
End If
End If
Next lngCol
Next lngRow
RtEqTot = dblTot
Else
RtEqTot = CVErr(vbError)
End If

End Function

John replied to Harry Boughen on 10-Jan-13 04:35 AM
thank you sir
John replied to wally eye on 10-Jan-13 04:36 AM
thank you sir