Do I need a two dimensional array for this?

[hotherps]

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("KB")
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("KB")
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("KB")
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

 

[Dick Kusleika]

hotherps

I think you can use the same array that you use for ICE. Every time you hit
a QAPK, add i to the array. By the time you get through all the ICE cells,
you will likely have duplicate values in your array, but that shouldn't
affect your Match function, it will just find the first one. But if it
finds one, it won't use it, so you're covered.

Wait, now I see the problem. If found, you add one to i and make that the
location for PACK. But that number may also be used for QA, right?

Here's one thing that I don't get. In this part of PACK,

arr(UBound(arr)) = i
found = 0
On Error Resume Next
found = WorksheetFunction.Match(i, arr, 0)
If found Then i = i + 1

aren't you guaranteeing that found will be <> 0? You add i to the array,
then try to match i in the array. Of course it will be there, you just
added it. It seems like in this case it wouldn't matter if i had been used
in ICE or not. Am I missing something here?

One way that you might do this is to get rid of the array and use the Find
method of the range object. Your PACK section might look like this:

Dim Rng as Range
Dim FndIC as Range
Dim FndQA as Range

Set Rng = Range("K10B325")

i = 100
For Each Row In Rows("10:325")
cnt = 0
For Each CELL In Row.Columns("KB")
If CELL.Value = "PACK" Then

Do
Set FndIC = Rng.Find("IC" & i,,,xlWhole)
Set FndQA = Rng.Find("QA" & i,,,xlWhole)

CELL.Value = "PK" & i
i=i+1
Loop Until FndIC Is Nothing And FndQA Is Nothing

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

The new part will start a loop and look for IC100 and QA100 in the range.
It will write PK100 to the cell and increment i by 1. If it doesn't find
IC100 or QA100 (they are both Nothing) then the loop stops. If it does find
either or both, it runs again using i=101 and will continue to run until it
can't find and IC or a QA with that location number.

One downside to this is that you will be writing a value to the cell even if
it's wrong - knowing that you will replace that value on the next, or
subsequent, loops. You should probably wrap that cell writing in an If
statement that mirrors the Loop Until statement so that it's only written
when you know the loop will end.

Also, you may need to add another AND to your Loop Until statement when i
gets to a certain number. I don't know if you have an upper threshold for
i, but if IC and QA use every available i, that loop will keep running until
it finds an unused value. That's not a problem unless you have, say, only
location numbers up 300 and the loop runs it up to 301.

I didn't test all of this, I just wanted to illustrate another way that you
could do it. If you use it and can't get it to work, you're welcome to
email me sample data and I can provide you with a tested solution.