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