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
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