# worksheetfunction.countif

#### jmersing

##### Well-known Member
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

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi J,

Make sure that you understand the difference between Cells and Range. Consider the slight change shown here:
Code:
``````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

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

Replies
11
Views
1K
Replies
1
Views
508
Replies
13
Views
482
Replies
5
Views
547
Replies
1
Views
451

1,221,418
Messages
6,159,795
Members
451,589
Latest member
Harold14

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back