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.
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.
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
        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
      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