This code stores a value for each number (which is a location) tha
starts with ICE. That prevents PACK fron using the same location numbe
later in the code. I now have a third category "QA". I can't figure ou
how to stop PACK from using the same location numbers as QA. Any hel
greatly appreciated

i = 125
For Each Row In Rows("10:325")
cnt = 0
For Each CELL In Row.Columns("K:DB")
If CELL.Value = "QAPK" Then
CELL.Value = "QA" & CStr(i)
cnt = cnt + 1
End If
Next CELL
If cnt 0 Then
i = i + 2
If i 135 Then i = 125
End If
Next Row

i = 100
For Each Row In Rows("10:325")
cnt = 0
For Each CELL In Row.Columns("K:DB")
If CELL.Value = "ICE" Then
ReDim Preserve arr(UBound(arr) + 1)
arr(UBound(arr)) = i
CELL.Value = "IC" & CStr(i)
cnt = cnt + 1
End If
Next CELL
If cnt 0 Then
i = i + 2
If i 144 Then i = 100
End If
Next Row

i = 100
For Each Row In Rows("10:325")
cnt = 0
For Each CELL In Row.Columns("K:DB")
If CELL.Value = "PACK" Then
ReDim Preserve arr(UBound(arr) + 1)
arr(UBound(arr)) = i
found = 0
On Error Resume Next
found = WorksheetFunction.Match(i, arr, 0)
If found Then i = i + 1
CELL.Value = "PK" & CStr(i)
cnt = cnt + 1
End If
Next CELL
If cnt 0 Then
i = i + 1

If i = 146 Then i = 100
End If
Next Ro

--
Message posted from http://www.ExcelForum.com