Countif - getting the wrong result

[jmersing]

What I'm trying to do is look down a row of text values that could have the value "ICE" in them. I would like to assign "IC & num" to the rows that ICE is listed in the enitre row.

3 problems doing that

The number of columns could change at any point

There will be values other than ICE i.e. Brk or Lun in the row, they can not be overwritten.

I can not change the number of columns. I just want to replace the value ICE if it is the only value other than Brk or Lun in the row.

any ideas?


here is what I was trying:
If WorksheetFunction.CountIf _
(.Range(rng, .cells(rng.Row, "AL")).Resize(1, 16), "ICE") >= 15 Then

cell.Resize(1, 16).Value = "IC" & num

[RANGE=cls:xl2bb-100][XR][XH=cs:11]SUPPORTMasterNew.xls[/XH][/XR][XR][XH][/XH][XH]G[/XH][XH]H[/XH][XH]I[/XH][XH]J[/XH][XH]K[/XH][XH]L[/XH][XH]M[/XH][XH]N[/XH][XH]O[/XH][XH]P[/XH][/XR][XR][XH]59[/XH][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]Brk[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]QAPK[/XD][XD=h:c|fz:8pt]QAPK[/XD][XD=h:c|fz:8pt]QAPK[/XD][XD=h:c|fz:8pt]QAPK[/XD][/XR][XR][XH]60[/XH][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][/XR][XR][XH]61[/XH][XD=h:c|fz:8pt]QAPK[/XD][XD=h:c|fz:8pt]QAPK[/XD][XD=h:c|fz:8pt]QAPK[/XD][XD=h:c|fz:8pt]QAPK[/XD][XD=h:c|fz:8pt]Lun[/XD][XD=h:c|fz:8pt]Lun[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][/XR][XR][XH]62[/XH][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]Lun[/XD][XD=h:c|fz:8pt]Lun[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]QAPK[/XD][XD=h:c|fz:8pt]QAPK[/XD][XD=h:c|fz:8pt]QAPK[/XD][XD=h:c|fz:8pt]QAPK[/XD][/XR][XR][XH]63[/XH][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][XD=h:c|fz:8pt]ICE[/XD][/XR][XR][XH=cs:11][RANGE][XR][XD]Wednesday[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][code]

 

[Norie]

I would like to assign "IC & num" to the rows that ICE is listed in the enitre row.

What do you mean by assign?

Where will you get the 'num' from?

 

[jmersing]

Here is the sub Norie, thanks


Dim arr(100 To 152) As String
Dim arr1, num
Dim arr2
Dim rng As Range, cell As Range
Dim i As Long, j As Long
arr1 = Array(125, 127, 129, 131, 133)
arr2 = Array(136, 134, 132, 130, 128, 126, 124, 122, 120, 118, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152)
Dim cells As Range

For i = 11 To 288
For Each cell In Rows(i).Columns("G:AL")
Set rng = Nothing
If cell.Value = "ICE" Then
Set rng = cell
Exit For
End If
Next
If Not rng Is Nothing Then
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" Then
cell.Value = "IC" & num
End If

Next
End If
Next

 

[Norie]

I'm sorry but that doesn't really help.

 

[jmersing]

Ok let me try to re-explain

I want to be able to test if the value ICE is in each of the rows before the value IC & numn is inserted in the row. There will be rows whwere ICE might appear once or twice. I do not want to include rows like that only rows that have nothing but ICE or have a combination of Ice Brk Lun.

When I use the count if it counts re-caluclates as it moves through the row. I just need to check the row once and then either use that row or go to the next.

Is that any clearer?