VBA code to compare integer values in an array.

A

Agoparaju

New Member
Joined
Oct 10, 2014
Messages
4
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
MINMAX
30 10901020
50 10901020
10 10901020
10 10203040506070809010011090059006901020
10 1020304050607090059006901020
10 1020304050607090059006901020
60 1020304050809010011090059006901020
10 102030405060809010011090059006901020
10 102030405060708090100110120130140150160230900090059006901020
10 1020304050900090059006901020
10 10203040506070809010011012013014090059006901020
10 10203040506070809010090059006901020
10 10203040506090059006901020
10 10203040506070809010011090059006901020
10 10203040506070809010011012013014015016090059006901020
10110

<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>
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Agoparaju,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


1. What is the worksheet name that contains your displayed data?

2. What cell contains MIN?

3. What cell contains MAX?

4. Can we have another screenshot (manually formatted by you) with the results you are looking for?


In order to assist you it would help us if you posted ALL your macro code using code tags.

When posting VBA code, please use Code Tags - like this:

[code=rich]

'Paste your code here.

[/code]
 
Upvote 0
Agoparaju,

Even better, and, so that we can get it right on the first go:


You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.
 
Upvote 0
Code: 
Sub Populate_OP()
Workbooks("B Down.xlsm").Activate
Sheets("DATA").Select
    If ActiveSheet.AutoFilterMode = True And ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData
Erase A
'*****************************READ IN DATA
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
    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
D(tp, 3) = Cells(i, 27) 'Max
[IMG]C:\Users\fn318746\Desktop\output.jpg[/IMG]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
GoTo Line20
Line10:
If C(j, 8) < D(i, 2) Then GoTo Line30
GoTo Line20
Line30:
If D(i, 3) = 0 Or D(i, 3) <= C(j, 8) Then GoTo Line40
GoTo Line20
Line40:
Cells(i, k) = C(j, 6)
Cells(i, k).Interior.Color = A(j, 7) 'add color status
k = k + 1
Line20:
j = j + 1: Loop
i = i + 1: Loop
End Sub


Hiker thank you for your response.
I am using PC,
Excel 2010
I cannot upload the excel file because of data sensitiivity


Min max cells are Columns 26 and 27

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

Output
MIn MAX

<TBODY>
</TBODY>
30</SPAN>
30</SPAN>
9010</SPAN>
50</SPAN>
50</SPAN>
9010</SPAN>
10</SPAN>
10</SPAN>
9010</SPAN>
10</SPAN>
10</SPAN>
20</SPAN>
30</SPAN>
40</SPAN>
50</SPAN>
60</SPAN>
70</SPAN>
80</SPAN>
90</SPAN>
100</SPAN>
110</SPAN>
9005</SPAN>
9006</SPAN>
9010</SPAN>
10</SPAN>
40</SPAN>
10</SPAN>
20</SPAN>
30</SPAN>
40</SPAN>
10</SPAN>
10</SPAN>
20</SPAN>
30</SPAN>
40</SPAN>
50</SPAN>
60</SPAN>
70</SPAN>
9005</SPAN>
9006</SPAN>
9010</SPAN>
60</SPAN>
150</SPAN>
60</SPAN>
80</SPAN>
90</SPAN>
100</SPAN>
110</SPAN>
10</SPAN>
10</SPAN>
20</SPAN>
30</SPAN>
40</SPAN>
50</SPAN>
60</SPAN>
80</SPAN>
90</SPAN>
100</SPAN>
110</SPAN>
9005</SPAN>
9006</SPAN>
9010</SPAN>
10</SPAN>
10</SPAN>
20</SPAN>
30</SPAN>
40</SPAN>
50</SPAN>
60</SPAN>
70</SPAN>
80</SPAN>
90</SPAN>
100</SPAN>
110</SPAN>
120</SPAN>
130</SPAN>
140</SPAN>
150</SPAN>
160</SPAN>
230</SPAN>
9000</SPAN>
9005</SPAN>
9006</SPAN>
9010</SPAN>
10</SPAN>
10</SPAN>
20</SPAN>
30</SPAN>
40</SPAN>
50</SPAN>
9000</SPAN>
9005</SPAN>
9006</SPAN>
9010</SPAN>
20</SPAN>

<TBODY>
</TBODY>




</SPAN>
 
Last edited:
Upvote 0
Agoparaju,

1. What is the worksheet name that contains your displayed data?

2. What cell contains MIN?

3. What cell contains MAX?
 
Upvote 0
hiker95 said:
Agoparaju,

1. What is the worksheet name that contains your displayed data?

2. What cell contains MIN?

3. What cell contains MAX?
Click to expand...


Burn down is the name of the work sheet,

Columns Z and AA contain the min and max values.

no you did not
Y
Part name </SPAN>		 Z AA AB AC ...
MIN</SPAN>MAX</SPAN>
qewqerwq</SPAN>30</SPAN> 50 30</SPAN> 40</SPAN>
wqrwqerwq</SPAN>50</SPAN> 90 60</SPAN> 80</SPAN>
ewqwqe</SPAN>10</SPAN> 70 10 </SPAN> 20</SPAN> </SPAN>
qwewqe</SPAN>10</SPAN> 80</SPAN> 20</SPAN></SPAN></SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL span=4></COLGROUP>
 
Upvote 0
Agoparaju,

One last try:

Is the following screenshot correct?

Min is in cell Z7, and, Max is in cell AA7?



Excel 2007
ZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAV
7MinMax
8309010
9509010
10109010
11102030405060708090100110900590069010
1210203040
1310203040506070900590069010
14608090100110
151020304050608090100110900590069010
161020304050607080901001101201301401501602309000900590069010
171020304050900090059006901020
18
BURN DOWN
 
Upvote 0
Agoparaju,

Do the numbers beginning in cell AB8, down, always contain numbers for all the columns to the right with data?


Right now I am using column AB to calculate the last used row for all the min's, and, max's that are returned
in range Z8:AA17 in this instance.


Sample raw data in worksheet BURN DOWN (not all columns are shown to fit the screenshot correctly):


Excel 2007
ZAAABACADAEAFAGAHAIAJAKALAMANAOAPAUAVAW
7MinMax
8309010
9509010
10109010
11102030405060708090100110900590069010
1210203040
1310203040506070900590069010
14608090100110
151020304050608090100110900590069010
1610203040506070809010011012013014015090069010
171020304050900090059006901020
18
BURN DOWN


After the macro:


Excel 2007
ZAAABACADAEAFAGAHAIAJAKALAMANAOAPAUAVAW
7MinMax
8309010309010
9509010509010
10109010109010
11109010102030405060708090100110900590069010
12104010203040
1310901010203040506070900590069010
1460110608090100110
151090101020304050608090100110900590069010
1610901010203040506070809010011012013014015090069010
171090101020304050900090059006901020
18
BURN DOWN


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub GetMinMax()
' hiker95, 10/10/2014, ME810920
Dim lr As Long, lc As Long
Application.ScreenUpdating = False
Sheets("BURN DOWN").Activate
lr = Cells(Rows.Count, "AB").End(xlUp).Row
lc = Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
If lr > 7 Then Range("Z8:AA" & lr).ClearContents
With Range("Z8:Z" & lr)
  .FormulaR1C1 = "=MIN(RC[2]:RC[" & lc - 2 & "])"
  .Value = .Value
End With
With Range("AA8:AA" & lr)
  .FormulaR1C1 = "=MAX(RC[1]:RC[" & lc - 27 & "])"
  .Value = .Value
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetMinMax macro.
 
Last edited:
Upvote 0
Hiker thank you ver much for your help. Though the out put is exactly what I am looking for the raw data is not presented in this way. I read the raw data from a differnt sheet ( "Data" Sheet in the code) store it in arrays A and C. I read the part number and the Min Max values from the sheet where we are going to print all the operations. ( Burn Down) Part numbers are in column Y, Min and Max are in column Z and AA. These are stored into Array B and D.

Now I compare the part numbers in Array A nd B, Use the min max values in array D as a control to print the operation numbers from array C..

This gives me the out put you have. Hope I explained it clearly this time.
 
Upvote 0
Agoparaju,

In order to continue, and, so that I can get it right this next time, I would like to see your actual workbook containing worksheets Data, and, what the results should look like in worksheet Burn Down:

You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.


If you are not able to provide the above, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
IF and AND formulas for multiple criteria
Replies
6
Views
153
Peter_SSs
Peter_SSs
K
  • Question
conditional formatting question
Replies
2
Views
149
kerm007
K
A
  • Solved
Application-defined or object defined Error when Filtering Listbox
Replies
4
Views
130
Peter_SSs
Peter_SSs
T
  • Question
Combining long formulas in Excel
2
Replies
18
Views
417
tzav
T
V
  • Question
need help in formula
Replies
4
Views
435
VBABEGINER
V

Forum statistics

Threads
1,214,515
Messages
6,119,973
Members
448,933
Latest member
Bluedbw

Share this page

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
Back
Top