I have a Min, Max values in two columns in excel. I also have a list of operations with a sequence number example(10,20,30,40 ......100) I am reading these values into Arrays.
And i am trying to display the operations within the min, max values beside the columns, but some how the < or greater than signs do not seem to work
I am reading the raw data into the array here,
P = 2
i = 2: Do Until Cells(i, 1) = ""
A(P, 1) = Cells(i, 8) 'DISPLAY SEQ
A(P, 2) = Cells(i, 13) 'planning code
A(P, 3) = "" 'Cells(i, 28) 'ORDER DESC
A(P, 4) = Cells(i, 4) 'PN
A(P, 5) = Cells(i, 3) 'WO
C(P, 6) = Cells(i, 8) 'SEQ this is the operation number I delcared the array as an integer
A(P, 7) = Cells(i, 21) 'SPACE TO LOAD COLORINDEX
A(P, 8) = Cells(i, 41) 'Exclude
A(P, 9) = Cells(i, 11) 'STATUS
A(P, 10) = Cells(i, 40) 'STATUS schedule
A(P, 11) = Cells(i, 10) 'order status
'**** SET COLOR BASED ON STATUS****
Select Case A(P, 9)
Case "X" 'ON HOLD
A(P, 7) = 5263615 'RED
Case "CLOSE" ' , "C*" 'COMPLETE
A(P, 7) = 12632256 'GREY
Case "IN QUEUE", "PENDING" 'NOT STARTED
A(P, 7) = 6750207 'YELLOW
Case "ACTIVE" 'GREEN
A(P, 7) = 6750054 'STARTED
End Select
P = P + 1
Line1000:
i = i + 1: Loop
Sheets("BURN DOWN").Select
tp = 7
i = 7: Do Until Cells(i, 25) = ""
B(tp, 1) = Cells(i, 25) 'Plan/Part number
D(tp, 2) = Cells(i, 26) 'Min form excel sheet declared this array as an integer
D(tp, 3) = Cells(i, 27) 'Max from excel sheet declared this array as an integer
tp = tp + 1
i = i + 1: Loop
Sheets("BURN DOWN").Select
Range("AB7:QF25000").Select: Selection.Clear
Selection.Font.Size = 7.5
Selection.Orientation = 0
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
'Display
i = 7: Do Until B(i, 1) = ""
k = 28
j = 2: Do Until A(j, 5) = ""
If B(i, 1) = A(j, 4) Then GoTo Line10 'comparing the part number here first
GoTo Line20
Line10:
If D(i, 2) < C(j, 8) Then GoTo Line30 'if the operation number is greater than the min value
GoTo Line20
Line30:
If D(i, 3) = "" Or D(i, 3) <= C(j, 8) Then GoTo Line40 'If max value is empty or operation number is less than max value
GoTo Line20
Line40:
Cells(i, k) = C(j, 6) ' Print operation muber in the adjacent cell
Cells(i, k).Interior.Color = A(j, 7) 'add color status
k = k + 1
Line20:
j = j + 1: Loop
i = i + 1: Loop
This is how the out put would look like, but obviously the MIN MAX values are having no effect. Please help me out
<COLGROUP><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" width=36><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1206" span=22 width=33><TBODY>
</TBODY>
And i am trying to display the operations within the min, max values beside the columns, but some how the < or greater than signs do not seem to work
I am reading the raw data into the array here,
P = 2
i = 2: Do Until Cells(i, 1) = ""
A(P, 1) = Cells(i, 8) 'DISPLAY SEQ
A(P, 2) = Cells(i, 13) 'planning code
A(P, 3) = "" 'Cells(i, 28) 'ORDER DESC
A(P, 4) = Cells(i, 4) 'PN
A(P, 5) = Cells(i, 3) 'WO
C(P, 6) = Cells(i, 8) 'SEQ this is the operation number I delcared the array as an integer
A(P, 7) = Cells(i, 21) 'SPACE TO LOAD COLORINDEX
A(P, 8) = Cells(i, 41) 'Exclude
A(P, 9) = Cells(i, 11) 'STATUS
A(P, 10) = Cells(i, 40) 'STATUS schedule
A(P, 11) = Cells(i, 10) 'order status
'**** SET COLOR BASED ON STATUS****
Select Case A(P, 9)
Case "X" 'ON HOLD
A(P, 7) = 5263615 'RED
Case "CLOSE" ' , "C*" 'COMPLETE
A(P, 7) = 12632256 'GREY
Case "IN QUEUE", "PENDING" 'NOT STARTED
A(P, 7) = 6750207 'YELLOW
Case "ACTIVE" 'GREEN
A(P, 7) = 6750054 'STARTED
End Select
P = P + 1
Line1000:
i = i + 1: Loop
Sheets("BURN DOWN").Select
tp = 7
i = 7: Do Until Cells(i, 25) = ""
B(tp, 1) = Cells(i, 25) 'Plan/Part number
D(tp, 2) = Cells(i, 26) 'Min form excel sheet declared this array as an integer
D(tp, 3) = Cells(i, 27) 'Max from excel sheet declared this array as an integer
tp = tp + 1
i = i + 1: Loop
Sheets("BURN DOWN").Select
Range("AB7:QF25000").Select: Selection.Clear
Selection.Font.Size = 7.5
Selection.Orientation = 0
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
'Display
i = 7: Do Until B(i, 1) = ""
k = 28
j = 2: Do Until A(j, 5) = ""
If B(i, 1) = A(j, 4) Then GoTo Line10 'comparing the part number here first
GoTo Line20
Line10:
If D(i, 2) < C(j, 8) Then GoTo Line30 'if the operation number is greater than the min value
GoTo Line20
Line30:
If D(i, 3) = "" Or D(i, 3) <= C(j, 8) Then GoTo Line40 'If max value is empty or operation number is less than max value
GoTo Line20
Line40:
Cells(i, k) = C(j, 6) ' Print operation muber in the adjacent cell
Cells(i, k).Interior.Color = A(j, 7) 'add color status
k = k + 1
Line20:
j = j + 1: Loop
i = i + 1: Loop
This is how the out put would look like, but obviously the MIN MAX values are having no effect. Please help me out
MIN | MAX | ||||||||||||||||||||||
30 | 10 | 9010 | 20 | ||||||||||||||||||||
50 | 10 | 9010 | 20 | ||||||||||||||||||||
10 | 10 | 9010 | 20 | ||||||||||||||||||||
10 | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 9005 | 9006 | 9010 | 20 | ||||||||
10 | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 9005 | 9006 | 9010 | 20 | ||||||||||||
10 | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 9005 | 9006 | 9010 | 20 | ||||||||||||
60 | 10 | 20 | 30 | 40 | 50 | 80 | 90 | 100 | 110 | 9005 | 9006 | 9010 | 20 | ||||||||||
10 | 10 | 20 | 30 | 40 | 50 | 60 | 80 | 90 | 100 | 110 | 9005 | 9006 | 9010 | 20 | |||||||||
10 | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 | 130 | 140 | 150 | 160 | 230 | 9000 | 9005 | 9006 | 9010 | 20 | |
10 | 10 | 20 | 30 | 40 | 50 | 9000 | 9005 | 9006 | 9010 | 20 | |||||||||||||
10 | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 | 130 | 140 | 9005 | 9006 | 9010 | 20 | |||||
10 | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 9005 | 9006 | 9010 | 20 | |||||||||
10 | 10 | 20 | 30 | 40 | 50 | 60 | 9005 | 9006 | 9010 | 20 | |||||||||||||
10 | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 9005 | 9006 | 9010 | 20 | ||||||||
10 | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 | 130 | 140 | 150 | 160 | 9005 | 9006 | 9010 | 20 | |||
10 | 110 | ||||||||||||||||||||||
<COLGROUP><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" width=36><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1206" span=22 width=33><TBODY>
</TBODY>