MerseaPete
02-01-2013, 08:27 AM
IThis excellent code almost solves my problem, but I want to take the numeric result displayed in the msgbox and past it (Ctrl/v) into a cell
Please help, its driving me nuts.
Thanks
Sub xlSumHiLited()
'
'************************************************************************** **************
' Target Application: MS Excel
' Function: sums all values in a selected range and displays:
' # of cells evaluated
' # of cells that were blank
' # of "bad" cells that were bypassed + content of those cells
' actual sum
'************************************************************************** **************
'
Dim Num As Integer, NumBad As Integer, NumBlank As Integer
Dim Cell As Range
Dim Total As Single
Dim strBuffer As String
Total = 0
Num = 0
NumBad = 0
NumBlank = 0
For Each Cell In Selection
On Error Resume Next
If Cell.Value <> "" Then
Total = Total + Cell.Value
Select Case Err
Case Is = 0
Num = Num + 1
Case Is <> 0
NumBad = NumBad + 1
strBuffer = strBuffer & " " & Cell.Value & vbCrLf
End Select
Else
NumBlank = NumBlank + 1
End If
Next Cell
Select Case NumBad
Case Is = 0
MsgBox "xlSumHiLited" & vbCrLf & vbCrLf & _
"# cells examined = " & (Num + NumBad + NumBlank) & vbCrLf & _
"# blank cells = " & NumBlank & vbCrLf & _
"# cells actually summed = " & Num & vbCrLf & vbCrLf & _
"SUMMED TOTAL = " & Total, vbInformation
Case Is > 0
MsgBox "xlSumHiLited" & vbCrLf & vbCrLf & _
"# cells examined = " & (Num + NumBad + NumBlank) & vbCrLf & _
"# blank cells = " & NumBlank & vbCrLf & _
"# cells actually summed = " & Num & vbCrLf & vbCrLf & _
"SUMMED TOTAL = " & Total & vbCrLf & vbCrLf & _
"# cells bypassed = " & NumBad & vbCrLf & _
"contents of bypassed cells:" & vbCrLf & _
strBuffer, vbInformation
End Select
End Sub
Please help, its driving me nuts.
Thanks
Sub xlSumHiLited()
'
'************************************************************************** **************
' Target Application: MS Excel
' Function: sums all values in a selected range and displays:
' # of cells evaluated
' # of cells that were blank
' # of "bad" cells that were bypassed + content of those cells
' actual sum
'************************************************************************** **************
'
Dim Num As Integer, NumBad As Integer, NumBlank As Integer
Dim Cell As Range
Dim Total As Single
Dim strBuffer As String
Total = 0
Num = 0
NumBad = 0
NumBlank = 0
For Each Cell In Selection
On Error Resume Next
If Cell.Value <> "" Then
Total = Total + Cell.Value
Select Case Err
Case Is = 0
Num = Num + 1
Case Is <> 0
NumBad = NumBad + 1
strBuffer = strBuffer & " " & Cell.Value & vbCrLf
End Select
Else
NumBlank = NumBlank + 1
End If
Next Cell
Select Case NumBad
Case Is = 0
MsgBox "xlSumHiLited" & vbCrLf & vbCrLf & _
"# cells examined = " & (Num + NumBad + NumBlank) & vbCrLf & _
"# blank cells = " & NumBlank & vbCrLf & _
"# cells actually summed = " & Num & vbCrLf & vbCrLf & _
"SUMMED TOTAL = " & Total, vbInformation
Case Is > 0
MsgBox "xlSumHiLited" & vbCrLf & vbCrLf & _
"# cells examined = " & (Num + NumBad + NumBlank) & vbCrLf & _
"# blank cells = " & NumBlank & vbCrLf & _
"# cells actually summed = " & Num & vbCrLf & vbCrLf & _
"SUMMED TOTAL = " & Total & vbCrLf & vbCrLf & _
"# cells bypassed = " & NumBad & vbCrLf & _
"contents of bypassed cells:" & vbCrLf & _
strBuffer, vbInformation
End Select
End Sub