1

Here I got problem return 1010 logical test. It should checking each cell in range, and exit loop if cell contain number. Return 1 if any cell in range contain value, return 0 if all cell is blank. I tried worksheet function CountIf, CountA, Not IsEmpty, IsText but result is different seems like the blank cell contain invisible string. IsNumeric works on single cell but when range included its not working. I also note the first time I got it run, it produce result, second run causing error. Please help, my range need to be in variable term. 

Sub Try()
Dim path As String, myfile As String, file As String
Dim wb As Workbook
Dim i As Integer
Dim NCell As Range
Dim IsNumber As Boolean

path = "E:\SouthNorth\"
myfile = path & "1979.xls"
file = Dir(myfile)

Set wb = Workbooks.Open(Filename:=path & file)
wb.Activate 'necessary?
i = 24
'here object defined error
For Each NCell In Worksheets("Sheet1").Range(Cells(i, 2), Cells(i, 4)) 
   If IsNumeric(NCell) Then
        IsNumber = True
        If IsNumber = True Then Exit For
    End If
Next NCell
Select Case IsNumber
Case True
wb.Worksheets("Sheet2").Range("B" & i) = 1
Case False
wb.Worksheets("Sheet2").Range("B" & i) = 0
End Select
End Sub
Community
  • 1
  • 1
Siti Sal
  • 119
  • 2
  • 12
  • `IsNumeric` judges a blank cell to be numeric. Is that your problem? – SJR Jun 14 '17 at 16:42
  • @SJR, no, IsNumeric work fine. I just want to show that all mentioned worksheet function doesn't work. Eg. 3 cells all blank but CountA() return 3. CountIf(" ") return 0, IsText() return True. So I proceed using IsNumeric()... – Siti Sal Jun 14 '17 at 18:51

3 Answers3

0

You can use 'CountBlank' - if the column range is always 3 cells then you can declare a boolean and subtract the blank count from 3, giving you a 0 (false) if all cells are blank or anything above 0 (true) if at least one cell is occupied:

Dim x As Boolean
    x = 3 - Application.WorksheetFunction.CountBlank(Worksheets("Sheet1").Range(Cells(i, 2), Cells(i, 4)))
    MsgBox x

If it's specific to numerical values (e.g. ignoring text) then just add to your IsNumeric line:

If IsNumeric(ncell) And Not ncell = "" Then
jamheadart
  • 5,047
  • 4
  • 32
  • 63
  • How to avoid the Boolean from giving Application defined/Object define error? I try this but not works; With wb x = 3 - Application.WorksheetFunction.CountBlank(Worksheets("Sheet1").Range(.Cells(i, 2), .Cells(i, 4))) MsgBox x End With – Siti Sal Jun 15 '17 at 04:13
  • If you're in a "With WB" clause then you need to 1) place . in front of "Worksheets" to append that worksheet to the WB and 2) remove the . from the cells and place .address after (otherwise compiler thinks you are trying "workbook.cells(i,2)" ... SO try: x = 3 - Application.WorksheetFunction.CountBlank(.Worksheets("Sheet1"‌​).Range(Cells(i, 2).address, .Cells(i, 4).address)) – jamheadart Jun 15 '17 at 15:20
  • Error 438, Object doesn't support this property – Siti Sal Jun 15 '17 at 15:49
  • I left a . in by mistake: x = 3 - Application.WorksheetFunction.CountBlank(.Worksheets("Sheet1‌​"‌​).Range(Cells(i, 2).address, .Cells(i, 4).address)) – jamheadart Jun 15 '17 at 16:35
  • Ya, remove a . infront of Cells() , then its works x = 3 - Application.WorksheetFunction.CountBlank(.Worksheets("Sheet1‌​‌​"‌​).Range(Cells(i‌​, 2).address, Cells(i, 4).address)) – Siti Sal Jun 16 '17 at 03:22
0

I think this belongs in a comment but I'm not allowed to...

You mentioned that your problem is when you try running it a second time, which means that the error could be from vba trying to open your file when it is already open. Everything else seems to work

Ignore all this, it was my original "answer" but I don't know how to format a strikethough and I don't want to delete it all.

Try this code

Sub Try()
    Dim wb As Workbook
    Dim path As String
    Dim i As Integer, j As Integer
    Dim NCell As Range

    path = "E:\SouthNorth\1979.xls"

    Set wb = Workbooks.Open(Filename:=path)
    wb.Activate
    i = 24
    Sheets("Sheet2").Range("B" & i).Value = 0

    For j = 2 To 4
        Set NCell = Sheets("Sheet1").Cells(i, j)
        If IsNumeric(NCell.Value) Then
            Sheets("Sheet2").Range("B" & i).Value = 1
            Exit For
        End If
    Next j

End Sub
Dexloft
  • 63
  • 7
  • I close the new opened wb before I run second time, It happen. Also, I create new module & try on new module, first run is Ok (result is wrong but at least give result), second time code stopped by error. It start happen when I work on different PC. Not sure if that's the reason. – Siti Sal Jun 14 '17 at 18:57
  • @SitiSal Please look at the code I added and let me know if it works – Dexloft Jun 14 '17 at 19:48
  • I don't mean to sound desperate even though I kind of am...would you mind marking my answer as correct or even just giving me an upvote? I'd greatly appreciate it :) – Dexloft Jun 15 '17 at 05:11
  • I appreciate yr answer and already vote early on. I'm sorry my reputation is under 15, it doesn't change the score. – Siti Sal Jun 15 '17 at 05:58
  • Oh I see, that's okay. Thank you! – Dexloft Jun 15 '17 at 12:55
0

To start off with, it is a really bad practice to leave the Cells defining a Range object without a parent worksheet. See Is the . in .Range necessary when defined by .Cells? for more information.

Worksheets("Sheet1").Range(Cells(24, 2), Cells(24, 4))

You seem to specifically want a count for numbers. Bringing in the worksheet's COUNT function will do this.

With Worksheets("Sheet1")
    With .Range(.Cells(24, 2), .Cells(24, 4))
        Worksheets("Sheet2").Range("B" & i) = Abs(CBool(Application.Count(.Cells)))
    End With
End With

You can also stay strictly within VBA with SpecialCells using xlCellTypeConstants with xlNumbers.

Dim rng As Range

With Worksheets("Sheet1")
    On Error Resume Next
    Set rng = .Range(.Cells(24, 2), .Cells(24, 4)).SpecialCells(xlCellTypeConstants, xlNumbers)
    On Error GoTo 0
    If Not rng Is Nothing Then
        Worksheets("Sheet2").Range("B" & i) = 1
    Else
        Worksheets("Sheet2").Range("B" & i) = 0
    End If
End With
  • I try SpecialCells but result is different. SpecialCells(xlCellTypeConstants, xlNumbers) all rows return 1 and using SpecialCells(xlCellBlanks) all rows return 0. But Thank you, Abs(CBool..) works fine. – Siti Sal Jun 15 '17 at 05:22