H

I've changed your sequencing so that all the Ice items are covered first. If there is an Ice item then the number is put into an array which is then checked each time that the Pack items are checked. This will mean that the Pack items will not get a number that is used by Ice

However, as your numbers loop, you don't explain what happens to the already used numbers. Should they be rebased as well

Hopefully the different approach will give you something to work with

Ton

Sub bbb(
Dim arr(
ReDim Preserve arr(0

i = 11
For Each cell In Range("A1:b200"

If cell.Value = "Ice" The
i = i +
ReDim Preserve arr(UBound(arr) + 1
arr(UBound(arr)) =
For j = 0 To
cell.Offset(0, j).Value = "Ice" & CStr(i
Next
If i 136 Then i = 11
End I
Next cel

i = 10
For Each cell In Range("A1:b200"
If cell.Value = "Pack" The
i = i +
found =
On Error Resume Nex
found = WorksheetFunction.Match(i, arr, 0
If found Then i = i +
For j = 0 To
cell.Offset(0, j).Value = "Pack" & CStr(i
Next
If i 145 Then i = 10
End I
Next cel

End Su


----- hotherps wrote: ----

I have this piece of code that assigns numbers to locations. One i
called "Pack" and the other "Ice". Pack has locations 100-14
Ice locations are 124 - 138 (increment by 2)

If Ice is not needed, the code should assign everything to "Pack"
which it does but, In that scenario the incremented numbers are bein
used twice. That can not happen, if a location is used in Ice it need
to be unavailable to Pack, how do I stop the duplicates
Here is the code and a small example of what it looks like
i = 10
For Each cell In Range("A1:DD2567"
If cell.Value = "Pack" The
i = i +
For j = 0 To
cell.Offset(0, j).Value = "Pack" & CStr(i
Next
If i 145 Then i = 10
End I
Next cel

i = 11
For Each cell In Range("A1:DD2567"

If cell.Value = "Ice" The
i = i +
For j = 0 To
cell.Offset(0, j).Value = "Ice" & CStr(i
Next
If i 136 Then i = 11
End I
Next cel

Pack101 Pack101 Pack101 Pack101 Pack101 Pack10
Pack102 Pack102 Pack102 Pack102 Pack102 Pack10

Pack103 Pack103 Pack103 Pack103 Pack103 Pack10
Pack Pack104 Pack104 Pack104 Pack104 Pack10
Ice114 Ice114 Ice114 Ice114 Ice114 Ice11
Ice116 Ice116 Ice116 Ice116 Ice116 Ice11
Pack Pack105 Pack105 Pack105 Pack105 Pack10
Pack Pack106 Pack106 Pack106 Pack106 Pack10

When Pack gets up to 114 It will assign it to Pack, but it has alread
been assigned to Ice. So it should skip that number
Thank


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