ReDim to store values so they are not used again
 

This code works well, as it fills the rows with the appropriate valu
based on what is in the cell. The redim stores the number used by IC
and prevents it from being used by PACK. I have 45 distinct location
so I can not use a location Csrt(i) twice.

I need to add a new task to the mix "QA" QA can only be placed in
locations 127-129-131-133 I need to add this to the bottom of the cod
and restrict it from using a location that was already used. Any ideas
It is alittle over my head.

Thanks

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
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 Row
:confused

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