worksheetfunction.countif

[jmersing]

I can't get the countif statement to work, I think I'm giving it an invalid range, but I'm not sure.

What I'm trying to do is after it is determined the cell.value = "ICE" then look ahead on the row 10 cells, if "Ice" is there more than 5 times then goto the next line.


For j = LBound(arr2) To UBound(arr2)
num = ""
If Len(Trim(arr(arr2(j)))) = 0 Then
num = arr2(j)
arr(num) = "IC"
Exit For
End If
Next
If num = "" Then num = "PPI"
For Each cell In Range(rng, .cells(rng.Row, "AL"))
If cell.Value = "ICE" _
And WorksheetFunction.CountIf(.cells(rng, .cells(rng.Row, "AL")).Resize(1, 10), "ICE") >= 5 Then
cell.Value = "IC" & num
End If
Next

 

[Richie(UK)]

Hi J,

Make sure that you understand the difference between Cells and Range. Consider the slight change shown here:

Sub Test()
    Dim rng As Range
    
    With ThisWorkbook.Worksheets("Sheet1")
        Set rng = .Range("A10")
'        If WorksheetFunction.CountIf _
'            (.Cells(rng, .Cells(rng.Row, "AL")).Resize(1, 10), "ICE") >= 5 Then
'            MsgBox "Yes!"
'        End If
        If WorksheetFunction.CountIf _
            (.Range(rng, .Cells(rng.Row, "AL")).Resize(1, 10), "ICE") >= 5 Then
            MsgBox "Yes!"
        End If
    End With
    
End Sub

HTH

 

[jmersing]

Thanks Richie! Now I see the difference bewteen the two, thanks for taking the time to explain it.