Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

sum numeric values in cells selection except text

0

Hi

I try making code to sum selection  for the cells contains number and exclude text.

when I select cells contains text then should show message ,otherwise should sum and show in A2, but my problem show message in all of about. how fix it,please?

Sub SumRangeCopy()
If Not IsNumeric(Selection) Then
MsgBox "please just select numbers", vbCritical: Exit Sub
Else
[A2] = Application.WorksheetFunction.Sum(Selection)
End If
End Sub
Answer
Discuss

Answers

0
Selected Answer

Hi again Leopard

Suggest you step through the selected range and test each cell, as shown in the revised code below (and in the attached revised file), chnages and comments in bold blow:

Sub SumRangeCopy()

    Dim Cl As Range

    ' loop through cells in selection
    For Each Cl In Selection
        ' say and stop if a text cell is found
        If Not IsNumeric(Cl) Then
            MsgBox "Please just select numbers" _
            & vbCr & "(.e.g. cell " & Cl.Address(0, 0) & " is non-numeric)", _
            vbCritical: Exit Sub
        End If
    Next Cl
    ' otherwise, set A2 value
    [A2] = Application.WorksheetFunction.Sum(Selection)

End Sub

This code runs with your original "sum" button.

Please note that the IsNumeric test checks if a value can be converted to a number (which may be true for a text value like '33). I've added a second button (turquoise, labelled "sum with text numbers") which is assigned to the revised code below (also in Module1) which WILL also sum text numbers):

Sub SumRangeIncTextNumbers()

    Dim Cl As Range, Sm As Double

    ' loop though selection
    For Each Cl In Selection
        ' say and stop if a text cell is found
        If Not IsNumeric(Cl) Then
            MsgBox "Please just select numbers" _
            & vbCr & "(.e.g. cell " & Cl.Address(0, 0) & " is non-numeric)", vbCritical: Exit Sub

            Else
            ' convert and add to sum
            Sm = Sm + CDbl(Cl.Value)
        End If
    Next Cl
    ' otherwise, set A2 value
    [A2] = Sm

End Sub

Hope this fixes your problem. If so, please mark the Answwer as Selected (as usual).

Discuss

Discussion

Hi John,
I hope you're fine.
well, show me without loop couldn't do that and this is what I think.
everything is great !
thank you so much.
leopard (rep: 90) May 22, '24 at 9:48 am
Hi Leopard. Glad that helped and thanks for selecting my Answer.

Things are okay with me and I hope you're fine too. Your aim might be possible without a loop but they don't delay much with such smaller data sets (and I like  loops!)

Incidentally did you get an email alert about my Answer? I ask since recently some users have made no reponse (perhaps because they didn't get alerts).
John_Ru (rep: 6212) May 22, '24 at 10:00 am
Incidentally did you get an email alert about my Answer?
in reality no .
I just depend on fresh teachexcel website and show me new alert because I work at my laptop.
this is problem if anybody wait  for notification by email !
leopard (rep: 90) May 22, '24 at 10:10 am
Hi guys, I was checking on this today and it does seem weird because some alerts come through to some people. I will see if its a backend email setting that might be causing it.
don (rep: 1989) May 22, '24 at 10:50 am
@Leopard - thanks for the feedback on emails (I suspected something was going wrong).

@Don - hope you find the problem. In my case I've not received email alerts since 21 November 2022(!) despite several attempts to rest by turning them off and on agan. Oddly I do get notifications on threads  from before that date on the very rare occasion that someone commnets on an old subject.
John_Ru (rep: 6212) May 22, '24 at 12:44 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login