Increment through For Next loop
 

I have this piece of code that assigns numbers to locations. One i
called "Pack" and the other "Ice". Pack has locations 100-145
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 = 100
For Each cell In Range("A1:DD2567")
If cell.Value = "Pack" Then
i = i + 1
For j = 0 To 8
cell.Offset(0, j).Value = "Pack" & CStr(i)
Next j
If i 145 Then i = 100
End If
Next cell

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

If cell.Value = "Ice" Then
i = i + 2
For j = 0 To 8
cell.Offset(0, j).Value = "Ice" & CStr(i)
Next j
If i 136 Then i = 112
End If
Next cell

Pack101 Pack101 Pack101 Pack101 Pack101 Pack101
Pack102 Pack102 Pack102 Pack102 Pack102 Pack102

Pack103 Pack103 Pack103 Pack103 Pack103 Pack103
Pack Pack104 Pack104 Pack104 Pack104 Pack104
Ice114 Ice114 Ice114 Ice114 Ice114 Ice114
Ice116 Ice116 Ice116 Ice116 Ice116 Ice116
Pack Pack105 Pack105 Pack105 Pack105 Pack105
Pack Pack106 Pack106 Pack106 Pack106 Pack106

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

Increment through For Next loop
 

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

Increment through For Next loop
 

Tony, that worked great! The only problem is I don't get it. I struggl
with the concept of arrays. I always have

Now, I have some other codes like "QA" that have to be in the mix. I'
going to try and write the rest of it myself.

In response to your question, I don't think I considered that.

Here is the logic behind the code. There are 146 stations, there are
different types of functions that can be performed in each i.e. Ice
Pack etc. Not all functions are always required. Each cell in th
spreadsheet that moves horozontily is representing a 15 minute tim
block. Meaning that the stations are assigned for 2 hour periods at
time. So the major concern is that the station number is never repeate
in a column where it has already been used. If all stations ar
occupied in a column, I'm not sure what to do at that point?

But assuming that does not happen the code should contine from th
begining until the end of the cell range.

Thanks again Tony

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

Increment through For Next loop
 

This entry was a mistake please disregar

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