If a range contains a value greater than 0

Question

The idea is to count the number of cells in a range and check each cell if the value is bigger than 0 if yes type "Overdue" otherwise type "Non overdue" Could someone help me?

my below code is not working

error 438/ object doesnt support this property or methode

show up in line where IF start.

Option Explicit

Sub investigate()
    Dim wb1 As Workbook
    Dim w As String
    Dim Name1 As String
    Dim Path1 As String
    Dim Lr As Integer

    w = 2  
    Name1 = ThisWorkbook.Sheets("vba").Cells(w + 4, 1).Text
    Path1 = ThisWorkbook.Sheets("Path").Cells(1, 2) & "Download\"

    Set wb1 = Workbooks.Open(Path1 & Name1)

    Lr = wb1.Sheets("Sheet1").Range("V" & Application.Rows.Count).End(xlUp).Row

    If Application.WorksheetFunction.CountIf(wb1.Range("V" & Lr), ">" & 0) > 0 Then
        ThisWorkbook.Sheets("vba").Cells(w + 4, 2).Value = "Overdue"
    Else 
        ThisWorkbook.Sheets("vba").Cells(w + 4, 2).Value = "No Overdue"
    End If

    wb1.Close
End Sub

`wb1` is a workbook and has no `.Range` (see `wb1.Range("V" & Lr)`) specify a sheet — Pᴇʜ, Jul 01 '19 at 10:32
thank you very much :-) - it works now here my code If Application.WorksheetFunction.CountIf(wb1.Sheets("Sheet1").Range("V" & Lr), ">" & 0) > 0 Then — ilyes, Jul 01 '19 at 10:39
Additionally I recommend to declare row counting variables as `Long` Excel has more rows than fit into `Integer`: Declare `Dim Lr As Long`. — Pᴇʜ, Jul 01 '19 at 12:20
You are only using one cell as your range. You will want to expand your range to possibly `(“V1:V”&Lr)` — Darrell H, Jul 01 '19 at 12:26

score 0 · Answer 1 · edited Jul 01 '19 at 12:14

I am not really sure your code will do what you want. If you are looking into counting how many are overdue and how many are not by saving re count in location Cells(w+ 4,2) you'd get the value overwritten.

The following will save the "overdue" title in cells(w+4,2) and cells(w+5,2) so they don't overlap. in the column to the right you will find the count of how many of these payments.

hope you enjoy

Option Explicit

Sub investigate()
    Dim wb1 As Workbook
    Dim w As Integer
    Dim Name1 As String
    Dim Path1 As String
    Dim Lr As Integer
    Dim overdueyes as long
    Dim overdueno as long

    w = 2  
    Name1 = ThisWorkbook.Sheets("vba").Cells(w + 4, 1).Text
    Path1 = ThisWorkbook.Sheets("Path").Cells(1, 2) & "Download\"

    Set wb1 = Workbooks.Open(Path1 & Name1)

    Lr = wb1.Sheets("Sheet1").Range("V" & Application.Rows.Count).End(xlUp).Row
    ThisWorkbook.Sheets("vba").Cells(w + 4, 2).Value = "Overdue"
    ThisWorkbook.Sheets("vba").Cells(w + 5, 2).Value = "No Overdue"
    for each cell in wb1.Sheets("Sheet1").range("V1:v" & lr)
        if cell.value > 0 then 
            overdueyes = overdueyes +1
        else
            overdueno = overdueyes +1
        end if
    next
    ThisWorkbook.Sheets("vba").Cells(w + 4, 3).Value = Overdueyes
    ThisWorkbook.Sheets("vba").Cells(w + 5, 3).Value = Overdueno

    wb1.Close
End Sub

I recommend to declare row counting variables as `Long` Excel has more rows than fit into `Integer`: Declare `Dim Lr As Long, w As Long`. Also there is no benefit in using `Integer` at all and you can [alwasy use `Long` instead](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520). — Pᴇʜ, Jul 01 '19 at 12:23
100% agree! I just tried to keep the code functional with least changes as possible. — Oliviero Domenighini, Jul 01 '19 at 13:21
ok thank you for all this advices I change it to Long :-) - I realy appreacite your support - thank you — ilyes, Jul 09 '19 at 07:02

If a range contains a value greater than 0

1 Answers1