CODEWITHSUNDEEP
vbaexcelworksheet-functioncountif
Mdh
Mdh

Reputation: 5

Countifs object not found error 424

I'm new here and new to vba. I was tryng to do a countif and if answer is over 0 than the criteria is respected, therefore I have double cheque issued problem on more than 500 rows.

Sub DoubleCheck() 
'Variables declaration
Dim row_rounter As Long, rngQ As Range, cel As Range, resDblChq As Variant,    rngB As Range, rngC As Range, rngF As Range, rngH As Range, rngI As Range
row_counter = Sheets("GW-DB").Cells(Cells.Rows.Count, "B").End(xlUp).Row 'row counter

'Range Initializatoin

 Set rngQ = Sheets("GW-DB").Range("Q2:Q" & row_counter)
 Set rngC = Sheets("GW-DB").Range("C2:C" & row_counter)
Set rngF = Sheets("GW-DB").Range("F2:F" & row_counter)
Set rngH = Sheets("GW-DB").Range("H2:H" & row_counter)
Set rngI = Sheets("GW-DB").Range("I2:I" & row_counter)
Set rngB = Sheets("GW-DB").Range("B2:B" & row_counter)
'Loop starts to validate possibility of double cheques

For Each cel In rngQ
' countif same name, same date,same amount, same reason
resDblChq = Application.WorksheetFunction.CountIfs(rngB, c.Offset(0, -16).Value, rngC, c.Offset(0, -15).Value, rngF, c.Offset(0, -12).Value, rngH, c.Offset(0, -9).Value, rngI, c.Offset(0, -8).Value)

' if it counts more than 0 it means there's a possibility of having double cheques issued
If resDblChq > 0 Then
c.Value = "Possible payment made twice"
End If
Next cel



End Sub

Thank You and I want to keep the most simple way for further modification

Upvotes: 0

Views: 373

Answers (3)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

c is undeclared, and because Option Explicit isn't specified, at run-time it's therefore an implicit Variant that contains vbEmpty:

    Debug.Assert Not IsEmpty(c) ' assertion fails here
    Debug.Print c.Offset(0, -16).Value '"object required"

Sure you can use Cel instead of c, but that won't prevent the myriad of other annoyingly easy-to-avoid run-time errors that merely specifying Option Explicit at the top of every single module would.

VBA will happily compile typos and declare them as on-the-fly implicit Variant variables without Option Explicit specified: this is NOT how you write reliable code. Use. Option. Explicit.

The reason why the error says "object required", is because syntactically, c.Offset(0, -16).Value can only ever be a Property Get or Function member call on an object named c - however IsObject(c) would be False, because vbEmpty isn't an object reference. Hence, object required.

Upvotes: 0

Satheesh K
Satheesh K

Reputation: 108

use cel instead of c

 resDblChq = Application.WorksheetFunction.CountIfs(rngB, cel.Offset(0, -16).Value, rngC, cel.Offset(0, -15).Value, rngF, cel.Offset(0, -12).Value, rngH, cel.Offset(0, -9).Value, rngI, cel.Offset(0, -8).Value)

Upvotes: 1

Jarom
Jarom

Reputation: 1077

In your loops you are referencing C instead of Cel. Try changing that like this. This will generate the error 424.

Sub DoubleCheck() 
'Variables declaration
Dim row_rounter As Long, rngQ As Range, cel As Range, resDblChq As Variant,    rngB As Range, rngC As Range, rngF As Range, rngH As Range, rngI As Range
row_counter = Sheets("GW-DB").Cells(Cells.Rows.Count, "B").End(xlUp).Row 'row counter

'Range Initializatoin

 Set rngQ = Sheets("GW-DB").Range("Q2:Q" & row_counter)
 Set rngC = Sheets("GW-DB").Range("C2:C" & row_counter)
Set rngF = Sheets("GW-DB").Range("F2:F" & row_counter)
Set rngH = Sheets("GW-DB").Range("H2:H" & row_counter)
Set rngI = Sheets("GW-DB").Range("I2:I" & row_counter)
Set rngB = Sheets("GW-DB").Range("B2:B" & row_counter)
'Loop starts to validate possibility of double cheques

For Each cel In rngQ
' countif same name, same date,same amount, same reason
resDblChq = Application.WorksheetFunction.CountIfs(rngB, cel.Offset(0, -16).Value, rngC, cel.Offset(0, -15).Value, rngF, cel.Offset(0, -12).Value, rngH, cel.Offset(0, -9).Value, rngI, cel.Offset(0, -8).Value)

' if it counts more than 0 it means there's a possibility of having double cheques issued
If resDblChq > 0 Then
cel.Value = "Possible payment made twice"
End If
Next cel



End Sub

Upvotes: 0

Related Questions