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

# 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
``````

0

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

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

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!)

John_Ru (rep: 6212) May 22, '24 at 10:00 am
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