Use all numbers in a For Loop

jmersing

Well-known Member
Joined
Apr 14, 2004
Messages
887
What I'm trying to do is make sure that 100-152 are used as much as possible. Currently if I run my code I get a result like this. No number can be repeated in a single column.

100 100 100 100
101 101 101 101 101 101
102 102 102 102 102 102
103 103 103 103 103 103 103 103
104 104 104 104 104 104 104 104

See how 100 is only placed 4 times. The end result is that the lower valued numbers are used much less than the higher ones. I need to try and balance them. If my code was working it would look like the image I pasted.

I'm looking for the result like in I8 it should check to see that 100 is available to use again
PostStations.xls
ABCDEFGHIJKLMNOPQR
1100100100100100100100100<
2101101101101101101101101
3102102102102102102102102
4103103103103103103103103
5104104104104104104104104
6105105105105105105105105
7106106106106106106106106<
8>100100100100100100100100
9101101101101101101101101
10102102102102102102102102
11103103103103103103103103
12>107107107107107107107107
13108108108108108108108108
Sheet1
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Te4t0n

New Member
Joined
Jan 23, 2005
Messages
21
Need to see your code or a detailed description which will explain what the numbers are and why they need to be what ever order they are in.
 

jmersing

Well-known Member
Joined
Apr 14, 2004
Messages
887
The idea is to assign numbers to three different type of text names that could appear on the worksheet. Those being PACK - ICE - QAPK

The range of columns is a 24 hour period in 15 minute increments.

In the arrays certain number values are associated with the tex values mentioned above.

What ends up happening is let's say were are referring to PACK, it will assign the first value (PK100) and move over column by column until the criteria is no longer met. Then it goes to the nexr row and continues on.

If PK100 could be used again because it has not nbenn used in the current column, it should. My code goes to the next number i.e. PK101, PK102 etc.
Make sense?

This is one of 3 Subs I use

Sub Assign1()

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
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
If InStr("119-133-135-137-139", Trim(Str(j))) Then GoTo skip
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
End Sub
 

Forum statistics

Threads
1,147,844
Messages
5,743,510
Members
423,800
Latest member
IuneKeiki

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