How to sum first digit of series of numbers

Question

the following numbers begin in cell B5: 23 34 45 56 45 54 I want to sum the first digit of each, and display the result in cell C9

I tried different adjustments to the code below.

Sub sum_first_digit()

    Dim colnum As Integer
    Dim sumfirst As Integer

    sumfirst = 0

    For colnum = 2 To 7 Step 1
        cellref = Cells(5, colnum)
        sumfirst = sumfirst + (Left(celref, 1))
    Next colnum

    Range("C9").Value = sumfirst


End Sub

Expected cell C9 to have the number 23.

Actual is

Runtime Error 13: Type Mismatch

You have hit the jack pot in solutions. You should kindly accept one (please see the check mark box next to the solution that happens to tickle your fancy) — urdearboy, Jan 18 '19 at 05:10

score 4 · Answer 1 · 2019-01-18T05:58:02.450

4

I don't know why a formula couldn't work. In C9 use,

=SUMPRODUCT(--LEFT(B5:G5&0))
'an array formula that will skip over blank and text cells
=SUM(IFERROR(--LEFT(B5:G5&0), 0))

edited Jan 18 '19 at 05:58

answered Jan 18 '19 at 02:01

1

Because it will throw an error when one of the cells is blank. – Variatus Jan 18 '19 at 02:10
1

Try the amended formula above. – Jan 18 '19 at 02:36
I love your idea and upvoted it! Should the user enter a text value by mistake having the formula return a #VALUE! error may, in fact, be beneficial. – Variatus Jan 18 '19 at 02:49
If they entered 'a text value by mistake' then perhaps they should receive a #VALUE error and not a false numerical value. However, some sort of array formula could easily provide error control. – Jan 18 '19 at 02:57
@Variatus, wouldn't a function possibly with the range passed in as an argument be more useful than a sub procedure? (btw, I'm not allowed to upvote) – Jan 18 '19 at 03:01
++ Always prefer formulas over VBA :) – Siddharth Rout Jan 18 '19 at 04:51
@SiddharthRout - only when the formula solution is simple. Overly complicated formulas solutions can have the calculation overhead reduced significantly by vba. I was looking at https://stackoverflow.com/questions/54246535/excel-formula-sum-time-lapses-based-on-criteria earlier and saw it as a vba solution, not a formula although a complicated formula could do it. – Jan 18 '19 at 06:00
True that. But it also depends for whom am I creating it. Many of my clients do not have macros enabled in their Office PCs. They do not worry about overheads then... They are happy to go ahead with formulas... LOL – Siddharth Rout Jan 18 '19 at 06:03
Yeah well, some clients you have to over complicate a formula for just because they will try and edit an array formula and cannot comprehend the CSE part. – Jan 18 '19 at 06:04
It seems a matter of taste. For most instances I prefer a worksheet to be either VBA-driven or using worksheet functions. Hybrids are more difficult to maintain because they require two sets of skills. Talking of maintenance VBA is preferable because it enables programming annotations as well as being faster to write and easier to read than complex formulas. – Variatus Jan 18 '19 at 09:07

VBasic2008 · Answer 2 · 2019-01-18T03:38:55.610

Sum of First Digits

The Function

Function SFD(Range As Range) As Long
    Dim vnt As Variant, i As Long, j As Long
    vnt = Range
    For i = 1 To UBound(vnt)
        For j = 1 To UBound(vnt, 2)
            If IsNumeric(vnt(i, j)) Then
                If CLng(vnt(i, j)) > 0 Then SFD = SFD + CLng(Left(vnt(i, j), 1))
            End If
        Next
    Next
End Function

In cell C9 use the formula: =SFD(B5:G5).

Array Version

Sub SumOfFirstDigit()

    Const cRow As Long = 5
    Const cCol1 As Variant = 2
    Const cCol2 As Variant = 7
    Const cTarget As String = "C9"

    Dim j As Long
    Dim sumFirst As Long
    Dim vnt As Variant

    vnt = Range(Cells(cRow, cCol1), Cells(cRow, cCol2))

    For j = 1 To UBound(vnt, 2)
        If IsNumeric(vnt(1, j)) Then
            If CLng(vnt(1, j)) > 0 Then _
                    sumFirst = sumFirst + CLng(Left(vnt(1, j), 1))
        End If
    Next

    Range(cTarget).Value = sumFirst

End Sub

Range Version

Sub SumOfFirstDigit2()

    Const cRow As Long = 5
    Const cCol1 As Variant = 2
    Const cCol2 As Variant = 7
    Const cTarget As String = "C9"

    Dim j As Long
    Dim sumFirst As Long

    For j = cCol1 To cCol2
        If IsNumeric(Cells(cRow, j)) Then
            If Cells(cRow, j) > 0 Then _
                    sumFirst = sumFirst + CLng(Left(Cells(cRow, j), 1))
        End If
    Next

    Range(cTarget).Value = sumFirst

End Sub

Quick Repair

You forgot Dim cellref As Range.
You forgot Set in Set cellref = Cells(5, colnum).
You misspelled celref in sumfirst = sumfirst + (Left(cellref, 1)).
You forgot Set cellref = Nothing
Integer is dead, Long prevails.

The Code

Sub sum_first_digit()

    Dim cellref As Range
    Dim colnum As Long
    Dim sumfirst As Long

    sumfirst = 0

    For colnum = 2 To 7
        Set cellref = Cells(5, colnum)
        sumfirst = sumfirst + (Left(cellref, 1))
    Next colnum

    Range("C9").Value = sumfirst

    Set cellref = Nothing

End Sub

score 2 · Answer 3 · answered Jan 18 '19 at 04:51

2

I would prefer to use formulas as @user10931127 suggested but if you still want VBA code then try this one line VBA code

Sub Sample()
    [C9] = [INDEX(SUM(VALUE(LEFT(A1:A6,1))),)]
End Sub

If you want an explanation how this works then see THIS

answered Jan 18 '19 at 04:51

Siddharth Rout

147,039
17
206
250

score 1 · Answer 4 · answered Jan 18 '19 at 01:58

1

Sub sum_first_digit()

Dim Col As Long, X As Long

For Col = 2 To 7
    X = X + Left(Cells(5, Col), 1)
Next Col

Range("C9") = X

End Sub

answered Jan 18 '19 at 01:58

urdearboy

14,439
5
28
58

What if the one of the cells is blank or has text in it? – Variatus Jan 18 '19 at 02:10
Will research further to understand how this worked. Thanks! – smp201804 Jan 18 '19 at 02:10
1

@Variatus neither of those seemed to be the problem so I’m not sure why I would address every what if scenario – urdearboy Jan 18 '19 at 02:12

score 1 · Answer 5 · answered Jan 18 '19 at 02:03

Please try this.

Sub sum_first_digit()

    Dim colnum As Integer
    Dim sumfirst As Integer
    Dim CelRef As Variant

    For colnum = 2 To 7
        CelRef = Left(Cells(5, colnum).Value, 1)
        sumfirst = sumfirst + Val(CelRef)
    Next colnum

    Range("C9").Value = sumfirst
End Sub

Michel Excel · Answer 6 · 2019-01-18T03:02:07.220

The problem is in the variable CellRef. First you wrote with 2 letters L and after with just 1 letter L.

Always use the comand Option Explicit at the top of your module. It forces you declare all variables and avoid this kind of error.

Correct code:

Sub sum_first_digit()

    Dim colnum As Integer
    Dim sumfirst As Integer
    Dim cellref As Variant 'Change this type to another more specific (int, long or string)

    sumfirst = 0

    For colnum = 2 To 7 Step 1
        cellref = Cells(5, colnum)
        If IsNumeric(Left(cellref , 1)) Then sumfirst = sumfirst + Left(cellref , 1)
    Next colnum

    Range("C9").Value = sumfirst

End Sub

The error is because the variable ** celref ** is not declared and therefore is empty. Left ("", 1) is also empty. This way you're trying to add a text to a number and that's why the types are incompatible.

score 1 · Answer 7 · answered Jan 18 '19 at 02:47

Try

sumfirst + (Left(celref, 1)) is String

sumfirst is Integer

integer = string --> error Type Mismatch

Sub sum_first_digit()

    Dim colnum As Integer
    Dim sumfirst As Integer

    sumfirst = 0

    For colnum = 2 To 7 Step 1
        cellref = Int(Cells(5, colnum) / 10)
        sumfirst = sumfirst + cellref
    Next colnum

    Range("C9").Value = sumfirst


End Sub