NEED TO RE-USE NUMBERS FROM ARRAY SO ALL ARE USED

jmersing

Well-known Member
Joined
Apr 14, 2004
Messages
887
This is the code I'm using it is used across a range of 96 columns There are 3 other modules that run the same code in different columns. If you look at the illustration I have posted you can see my problem. I want use all of the numerical values as I move through the rows.

Example: H1 ends at 100 with my current code 100 would not be used again in the range. It increments as it drops down each row. I want to be able to use every available number by having the code "reset" itself back to 100 and use any available numbers remaining.

Thanks


Dim arr(100 To 152) As String
Dim arr1, num
Dim arr2
Dim rng As Range, cell As Range
Dim i As Long, j As Long
Dim k As Long
arr1 = Array(125, 127, 129, 131, 133)
arr2 = Array(136, 134, 132, 130, 128, 126, 124, 122, 120, 118, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151)
Dim cells As Range
k = 127

With ActiveSheet


For i = 11 To 298 'was 11 TO 100
For Each cell In Rows(i).Columns("G:Z")
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, "Z"))
If cell.Value = "QAPK" Then
cell.Value = "QA" & num
End If
Next
End If
Next

For i = 11 To 298 ' WAS 11 TO 100
k = 127
For Each cell In Rows(i).Columns("G:Z")
Set rng = Nothing
If cell.Value = "ICE" Then
Set rng = cell
Exit For
End If
Next
If Not rng Is Nothing Then
For j = LBound(arr2) To UBound(arr2)
num = ""
If Len(Trim(arr(arr2(j)))) = 0 Then
num = arr2(j)
arr(num) = "IC"
Exit For
End If
Next
If num = "" Then num = "PPI"
For Each cell In Range(rng, .cells(rng.Row, "Z"))
If .cells(i, k).Value = "Y" Then
If cell.Value = "ICE" Then
cell.Value = "IC" & num

End If
End If
Next
End If
Next

For i = 11 To 298 'WAS 10 TO 100
For Each cell In Rows(i).Columns("G:Z")
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 151 Step 1 ' was 100 to 145
If InStr("119-133-135-137-139", Trim(Str(j))) Then GoTo skip 'was ("119-135-137-139",
num = ""

If Len(Trim(arr(j))) = 0 Then
num = j
arr(num) = "PK"
Exit For
End If
skip: Next
If num = "" Then num = "PPI"
For Each cell In Range(rng, .cells(rng.Row, "Z")) If cell.Value = "PACK" Then
cell.Value = "PK" & num
End If
Next
End If
Next
End With
PostStations.xls
ABCDEFGHIJKLMNOPQR
1100100100100100100100100<
2101101101101101101101101
3102102102102102102102102
4103103103103103103103103
5104104104104104104104104
6105105105105105105105105
7106106106106106106106106<
8>100100100100100100100100
9101101101101101101101101
10102102102102102102102102
11103103103103103103103103
12>107107107107107107107107
13108108108108108108108108
Sheet1
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,148,396
Messages
5,746,447
Members
424,020
Latest member
LongDoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top