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