Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do I need a two dimensional array for this?
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("K:DB") 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("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 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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do I need a two dimensional array for this?
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("K10:DB325") i = 100 For Each Row In Rows("10:325") cnt = 0 For Each CELL In Row.Columns("K:DB") 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. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup within a 2 dimensional array | Excel Worksheet Functions | |||
Three Dimensional Array Question | Excel Discussion (Misc queries) | |||
add to two dimensional array | Excel Programming | |||
2 Dimensional Array | Excel Programming | |||
2 Dimensional Array | Excel Programming |