Finding the smallest value

[johngio]

I have the following macro:

Sub test()
Dim iRow As Integer, iCol As Integer
For iCol = 7 To 9
For iRow = 16 To 279
If Cells(iRow, iCol) = "" Then
If Cells(iRow - 2, iCol) = Cells(iRow - 1, iCol) Or Cells(iRow - 2, iCol) = 0 Then
Cells(iRow, iCol) = Cells(iRow - 1, iCol)
Else
Cells(iRow, iCol) = Cells(iRow - 1, iCol) + Range("Q15")
End If
End If
Next iRow
Next iCol
End Sub


This results in an output of:

71642 19174 18888
71642 20974 18888
71642 22774 18888
71642 24574 18888
71642 26374 18888
71642 28174 18888
71642 29974 18888
71642 31774 18888
71642 33574 18888
71642 35374 18888
71642 37174 18888
71642 38974 18888
71642 40774 18888
71642 42574 18888
71642 44374 18888
71642 46174 18888
71642 47974 18888
71642 49774 18888
71642 51574 18888
71642 53374 18888
71642 55174 18888
71642 56974 18888
71642 58774 18888
71642 60574 18888
71642 62374 18888
71642 64174 18888
71642 65974 18888
71642 67774 18888
71642 69574 18888
71642 71374 18888
71642 73174 18888
71642 74974 18888
71642 76774 18888
71642 78574 18888
71642 80374 18888
71642 82174 18888
71642 83974 18888
71642 85774 18888
71642 87574 18888
71642 89374 18888
71642 91174 18888
71642 92974 18888
71642 94774 18888
71642 96574 18888
71642 98374 18888
71642 100174 18888
71642 101974 18888
71642 103774 18888
71642 105574 18888
71642 107374 18888
71642 109174 18888
71642 110974 18888
71642 112774 18888
71642 114574 18888
71642 116374 18888
71642 118174 18888
71642 119974 18888
71642 121774 18888
71642 123574 18888
71642 125374 18888
71642 127174 18888
71642 128974 18888
71642 130774 18888

However What I want to achieve is when any column reaches 100000, I would like to start adding onto the next smallest column, which would produce:


71642 19174 18888
71642 19174 18888
71642 19174 18888
71642 19174 18888
71642 19174 18888
71642 19174 18888
71642 19174 18888
71642 19174 18888
71642 19174 18888
71642 20974 18888
71642 22774 18888
71642 24574 18888
71642 26374 18888
71642 28174 18888
71642 29974 18888
71642 31774 18888
71642 33574 18888
71642 35374 18888
71642 37174 18888
71642 38974 18888
71642 40774 18888
71642 42574 18888
71642 44374 18888
71642 46174 18888
71642 47974 18888
71642 49774 18888
71642 51574 18888
71642 53374 18888
71642 55174 18888
71642 56974 18888
71642 58774 18888
71642 60574 18888
71642 62374 18888
71642 64174 18888
71642 65974 18888
71642 67774 18888
71642 69574 18888
71642 71374 18888
71642 73174 18888
71642 74974 18888
71642 76774 18888
71642 78574 18888
71642 80374 18888
71642 82174 18888
71642 83974 18888
71642 85774 18888
71642 87574 18888
71642 89374 18888
71642 91174 18888
71642 92974 18888
71642 94774 18888
71642 96574 18888
71642 98374 18888
71642 100174 18888
71642 100174 20688
71642 100174 22488
71642 100174 24288
71642 100174 26088
71642 100174 27888
71642 100174 29688
71642 100174 31488
71642 100174 33288
71642 100174 35088
71642 100174 36888

In order to do this I think I will have to put an If statement after
For iRow = 16 to 279


The problem I have here is how do I get Excel to recognise which is the smallest of the 3 columns? I could do it in a series of If statements, but this would be very long winded. Is there a minimum/lowest command I can use?

Basically I'm looking at:

If Cells(iRow-1,iCol)>100,000 Then
ADD Q14 to the smallest value and keep the other two values the same.

Once this is done the rest of the macro should still be ok

Any info would be appreciated.

Cheers

John

 

[johngio]

I think I may have a solution to my problem. I will insert a series of 3 IfAnd statements;

For example
If Cell(20,7)<Cell(20,8) And Cell(20,7)<Cell(20,9) Then
Cell (21,7)=Cell(20,7)+Range("Q15")

Only problem is I can't figure out how to combine If And statements in my macro

Any pointers? ?

Cheers

John

 

[johngio]

Don't know what I was doing; the If And is working now :P