I have a for next loop that assigns a string value, there are 3 string
in the array. There is also one 3 digit number per string.
Iwant to be able to list all of the strings but never repeat it's
digit number. All three strings should be available for use at any tim
but if the 3 digit number exists in the next column to be filled, i
should increment to the next highest value.

I hope I'm explaining this right, I'm really struggling with it.

Thanks:confused

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

Does this data exist on a worksheet?

Could the numbers be generated as the array is generated?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"hotherps " wrote in message
...
I have a for next loop that assigns a string value, there are 3 strings
in the array. There is also one 3 digit number per string.
Iwant to be able to list all of the strings but never repeat it's 3
digit number. All three strings should be available for use at any time
but if the 3 digit number exists in the next column to be filled, it
should increment to the next highest value.

I hope I'm explaining this right, I'm really struggling with it.

Thanks


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

if it will be in only one row you could have a number that stays th
same and you could just add the column number to it when the value get
assigned. Would that work? - Piku

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

That would probably not work because the numbers are specific. they
range from 100 to 145. Let me show you the code:

i = 112
For Each Row In Rows("10:324")
cnt = 0
For Each cell In Row.Columns("K:DB")
If cell.Value = "Value1" Then
ReDim Preserve arr(UBound(arr) + 1)
arr(UBound(arr)) = i
cell.Value = "V1" & CStr(i)
cnt = cnt + 1
End If
Next cell
If cnt 0 Then
i = i + 2
If i 136 Then i = 112
End If
Next Row

i = 100
For Each Row In Rows("10:324")
cnt = 0
For Each cell In Row.Columns("K:DB")
If cell.Value = "Value2" Then
found = 0
On Error Resume Next
found = WorksheetFunction.Match(i, arr, 0)
If found Then i = i + 1
cell.Value = "V2" & CStr(i)
cnt = cnt + 1
End If
Next cell
If cnt 0 Then
i = i + 1

If i = 145 Then i = 100
End If
Next Row


THis code works as far as preventing Value 2 from using a previously
used number. However Value 2 will not conform the same way, it repeats
itself quite often.
Thanks


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

Bob, I'm not sure if that would work or not. I'm really at the limit o
my VBA experience. I've never tried to do this before. But, the numbe
is defineable it has a fixed range, and yes it is on a worksheet

Thank

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

I'm not sure if my last posting was received. Bob I responded back t
you, I'm not sure if you got it or not.

my question is how to place an additonal array to duplicate th
behavior of my first one?

Should I try a different route?

Thank

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

Your code doesn't seem to work as you have described in past posts. It
duplicates numbers even among ICE and PACK. You have also stated that ICE
should only be between 124 and 138 as I recall, but in the same post, were
assigning it numbers outside that range. You also say you actually have 4
or 5 key words, such as BULK and QA, but are piecemealing them into your
posts.

What you need to do is work up a complete specification of what your rules
are and someone might be able to give you a comprehensive solution. Also,
it appears possible that your data could exhaust the available numbers - but
don't say what to do about that. You had some requirement that some numbers
had to increment by 2 - again, inconsistent in your string of posts. An
additional consideration is that it is not completely clear, but there could
be different numbers in the same row for different columns. Anyway, unless
this is a completely new problem - and it doesn't appear to be, you are
wasting peoples time putting a question that does not describe completely
what your situation is.

--
Regards,
Tom Ogilvy

"hotherps " wrote in message
...
That would probably not work because the numbers are specific. they
range from 100 to 145. Let me show you the code:

i = 112
For Each Row In Rows("10:324")
cnt = 0
For Each cell In Row.Columns("K:DB")
If cell.Value = "Value1" Then
ReDim Preserve arr(UBound(arr) + 1)
arr(UBound(arr)) = i
cell.Value = "V1" & CStr(i)
cnt = cnt + 1
End If
Next cell
If cnt 0 Then
i = i + 2
If i 136 Then i = 112
End If
Next Row

i = 100
For Each Row In Rows("10:324")
cnt = 0
For Each cell In Row.Columns("K:DB")
If cell.Value = "Value2" Then
found = 0
On Error Resume Next
found = WorksheetFunction.Match(i, arr, 0)
If found Then i = i + 1
cell.Value = "V2" & CStr(i)
cnt = cnt + 1
End If
Next cell
If cnt 0 Then
i = i + 1

If i = 145 Then i = 100
End If
Next Row


THis code works as far as preventing Value 2 from using a previously
used number. However Value 2 will not conform the same way, it repeats
itself quite often.
Thanks


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

I apologize if I have been unclear stating what I am trying to do. Thos
requirements have changed since I began doing this. Let me try t
explain the parameters exactly as they are. And again I apologize if
appear to be using this site improperly, that is not my intention.

I have 45 locations where items are packed for delivery.
There are different types of orders. Those being Ice - Pack - and QA
there are others but they are not assigned locations and should b
ignored always.
Ice items can only be packed in locations 100 - 138 but only the eve
numbered locations i.e. 100-102-104-106 etc.

Pack items can be packed in locations 100 - 145 incrementing by on
100-101-102 etc.

QA items can only be packed in 125-127-129-131-133 always

Pack items are always available
Ice items occur only a few days a week
QA items are always available

An Ice locations can also be used as a Pack station - but not vic
versa. Ice can only be done in the locations listed.

QA can only be done in QA

If the maximum amount of locations are filled in a column for Ic
any/all remaining locations can be filled by PACK. If both Pack and Ic
are at their capacity a string value should be applied "PPI". Q
always stays the same parameters after the five are assigned it shoul
ignore the rest.

I hope I am being clear enough, I promise you that is the entire tas
at hand.

Thank you for your patience
Ji

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

You still left a lot out (specifically layout and rules related to columns),
but I used some of your past posts - hopefully it will do approximately what
you want.

as written, a location is unique to a row of data. In otherwords you won't
have
col K col L
row 20 IC102
row 21 PA102

You would have
col K col L
row 20 IC102
row 21 PA103

102 would appear nowhere else but row20, 103 nowhere else but row 21. This
appeared to be the approach in the previous code.


if that is not the case, then you would need to process each column
separately - the code would require some modification to do that.

Sub TDDDD()
Dim arr(100 To 145) As String
Dim arr1, num
Dim rng As Range, cell As Range
Dim i As Long, j As Long
arr1 = Array(125, 127, 129, 131, 133)

For i = 1 To 235
For Each cell In Rows(i).Columns("K:DB")
Set rng = Nothing
If cell.Value = "QAPK" Then
Set rng = cell
Exit For
End If
Next
If Not rng Is Nothing Then
For j = LBound(arr1) To UBound(arr1)
num = ""
If Len(Trim(arr(arr1(j)))) = 0 Then
num = arr1(j)
arr(num) = "QA"
Exit For
End If
Next
If num = "" Then num = "PK"
For Each cell In Range(rng, Cells(rng.Row, "DB"))
If cell.Value = "QAPK" Then
cell.Value = "QA" & num
End If
Next
End If
Next

For i = 1 To 235
For Each cell In Rows(i).Columns("K:DB")
Set rng = Nothing
If cell.Value = "ICE" Then
Set rng = cell
Exit For
End If
Next
If Not rng Is Nothing Then
For j = 100 To 138 Step 2
num = ""
If Len(Trim(arr(j))) = 0 Then
num = j
arr(num) = "IC"
Exit For
End If
Next
If num = "" Then num = "PPI"
For Each cell In Range(rng, Cells(rng.Row, "DB"))
If cell.Value = "ICE" Then
cell.Value = "IC" & num
End If
Next
End If
Next


For i = 1 To 235
For Each cell In Rows(i).Columns("K:DB")
Set rng = Nothing
If cell.Value = "PACK" Then
Set rng = cell
Exit For
End If
Next
If Not rng Is Nothing Then
For j = 100 To 145 Step 1
num = ""
If Len(Trim(arr(j))) = 0 Then
num = j
arr(num) = "PA"
Exit For
End If
Next
If num = "" Then num = "PPI"
For Each cell In Range(rng, Cells(rng.Row, "DB"))
If cell.Value = "PACK" Then
cell.Value = "PA" & num
End If
Next
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"hotherps " wrote in message
...
I apologize if I have been unclear stating what I am trying to do. Those
requirements have changed since I began doing this. Let me try to
explain the parameters exactly as they are. And again I apologize if I
appear to be using this site improperly, that is not my intention.

I have 45 locations where items are packed for delivery.
There are different types of orders. Those being Ice - Pack - and QA ,
there are others but they are not assigned locations and should be
ignored always.
Ice items can only be packed in locations 100 - 138 but only the even
numbered locations i.e. 100-102-104-106 etc.

Pack items can be packed in locations 100 - 145 incrementing by one
100-101-102 etc.

QA items can only be packed in 125-127-129-131-133 always

Pack items are always available
Ice items occur only a few days a week
QA items are always available

An Ice locations can also be used as a Pack station - but not vice
versa. Ice can only be done in the locations listed.

QA can only be done in QA

If the maximum amount of locations are filled in a column for Ice
any/all remaining locations can be filled by PACK. If both Pack and Ice
are at their capacity a string value should be applied "PPI". QA
always stays the same parameters after the five are assigned it should
ignore the rest.

I hope I am being clear enough, I promise you that is the entire task
at hand.

Thank you for your patience
Jim


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

Tom, first off let me thank you for helping me out. I really want t
learn this type of coding but is a little out of my reach at th
moment. I'm attaching a copy of the file I'm working in in there ther
are 3 tabs One that has the result of my code, one with yours and th
original data for copying over if nessecary.

I got a strange result on yours. It only added numbers to a few of th
locations, in most cases it looks like the PA and PPI string have bee
joined without the location number?

Probably my fault by not exoplaining it correctly. If you look at m
tab, as each person is assigned a location my numbers increment. Th
problem is the increment too much and begin repeating themselves. Th
main goal is to assign all of the given locations per column withou
repeating a location number in the same column.

Thank you very much Tom for your assistance I'm really stranded here a
th emoment.

:

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

You didn't attach anything. Mine doesn't add PPI until it runs out of
numbers - you said to append ppi at that point, which I interpreted to mean
without a location number since there is none to assign.

--
Regards,
Tom Ogilvy

"hotherps " wrote in message
...
Tom, first off let me thank you for helping me out. I really want to
learn this type of coding but is a little out of my reach at the
moment. I'm attaching a copy of the file I'm working in in there there
are 3 tabs One that has the result of my code, one with yours and the
original data for copying over if nessecary.

I got a strange result on yours. It only added numbers to a few of the
locations, in most cases it looks like the PA and PPI string have been
joined without the location number?

Probably my fault by not exoplaining it correctly. If you look at my
tab, as each person is assigned a location my numbers increment. The
problem is the increment too much and begin repeating themselves. The
main goal is to assign all of the given locations per column without
repeating a location number in the same column.

Thank you very much Tom for your assistance I'm really stranded here at
th emoment.

:)


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

Let me try to attach the file again Tom so you can see it.

Yes I do want the cell to read "PPI" if all available PACKS - QA - an
ICE are filled. But when I run the code i have values in the cells lik
"PAPPI" If it is out of the range it should read just "PPI"

Thank

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

Still no attachment

send the file to



I appended PPI in lieu of the number once they were exhausted. (thus rather
than PA144, you get PAPPI) If you just want PPI, that can easily be fixed.

--
Regards,
Tom Ogilvy

"hotherps " wrote in message
...
Let me try to attach the file again Tom so you can see it.

Yes I do want the cell to read "PPI" if all available PACKS - QA - and
ICE are filled. But when I run the code i have values in the cells like
"PAPPI" If it is out of the range it should read just "PPI"

Thanks


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