Using count, counta, countblank functions

Question

I've tried count, counta, and countblank functions for this code, but it doesn't work. My code is:

Sheet1.Activate
If WorksheetFunction.CountBlank(Range(Cells(3, 3), Cells(50, 3))) > 0 Then
MsgBox "First Enter Data!"
Else
...

I want excel to do some calculations if all of the cells in range C3 to C50 are containing a number, and return the msgbox if they aren't. All the other codes are true. I've checked them several times. The problem is that even when all of those cells have numbers, the msgbox appears. I've tried many ways, but it keeps going wrong. Please help me. Thanks a lot.

Are you sure you are looking at the rights sheet? – brettdj Mar 30 '14 at 10:06 — brettdj, Mar 30 '14 at 10:06

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

Edit:

1) if your numbers stored as text, use following code (it change cells format to "number format"):

Dim rng As Range
Set rng = ThisWorkbook.Worksheets("Sheet1").Range("C3:C50")

With rng
    .NumberFormat = "0.00"
    .Value = .Value

    If WorksheetFunction.Count(.Cells) <> .Cells.Count Then
        MsgBox "First Enter Data!"
    Else
        MsgBox "Everything is ok. All cells in range C3:C50 contains numbers"
    End If
End With

2) You can also use this one:

Dim c As Range
Dim isAllNumbers As Boolean
isAllNumbers = True

For Each c In ThisWorkbook.Worksheets("Sheet1").Range("C3:C50")
    If Not IsNumeric(c) Or c = "" Then
        isAllNumbers = False
        Exit For
    End If
Next

If Not isAllNumbers Then
    MsgBox "First Enter Data!"
Else
    MsgBox "Everything is ok. All cells in range C3:C50 contains numbers"
End If

You may also want to read this: How to avoid using Select/Active statements

edited May 23 '17 at 12:05

Community

1
1

answered Mar 30 '14 at 08:54

Dmitry Pavliv

35,333
13
79
80

Thanks Simoco. But it returns the msgbox EVEN there are numbers in those cells. :-( – user3466971 Mar 30 '14 at 09:08
what message it returns? I've added in code additional msgbox when ALL cells are numbers `"Everything is ok, ALL cells in range C3:C50 contains numbers"`. You can remove it if you don't want to show this message – Dmitry Pavliv Mar 30 '14 at 09:10
No. It returns "First enter data! Some cells are...". I have no idea why. – user3466971 Mar 30 '14 at 09:57
maybe your cells are stored as text or you have additional spaces in cells, like `"1 "` instead `"1"`. In that case excel count your values as text, but not numbers. See my updated answer – Dmitry Pavliv Mar 30 '14 at 10:08

Using count, counta, countblank functions

1 Answers1