Else If Syntax Error

[johngio]

Hi all,

I'm trying to use the following piece of code and keep getting a Syntax error for the Else If command.

Sub Macro1()

Dim iRow As Integer

For iRow = 16 To 50
If Cells(iRow - 1, 7) - Cells(iRow, 7) = Cells(iRow - 1, 7) Then

Cells(iRow, 7) = "0"

Else If

Cells(iRow - 1, 7) - Cells(iRow, 7) = "0" Then

Cells(iRow, 7) = "0"

Else

Cells(iRow, 7).Value = Cells(iRow - 1, 7) + Range("Q14")

End If

Next iRow

This is a valid command as far as I can tell. Any ideas why this might be happening?

Thanks

John

 

[Scott Huish]

Why are you still treating 0 as text, and why are you writing more code than necessary?

 

[johngio]

Oops.

Forgot to change that.

I'll do it now

 

[Scott Huish]

If you want to do columns 7,8, and 9, perhaps:

Sub test()
Dim iRow As Integer, iCol As Integer
For iCol = 7 To 9
    For iRow = 16 To 50
        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("Q14")
            End If
        End If
    Next iRow
Next iCol
End Sub

 

[johngio]

That works prefectly.

One last question. When the number reaches 100,000 I want to start increasing the value of the smallest column, such that it will look like this:

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 50

I will also extend well past 50 in the above statement.

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

 

[Scott Huish]

From duplicate post:

What I would like to do is combine these two, such that everytime a new row is reachedthe macro checks if the value is greater than 100000. If it is, this value is "locked" and Q15 is added to the next smallest column.

I'm not sure what this means exactly. In your example, after the second column exceed 100000, it should then add to the first column?

Can you explain what you are trying to accomplish exactly? What is in Q15?
You have shown what your output is supposed to look like, but what does the original data look like?

 

[johngio]

What I have is 3 piles of material. Extra material is being added at the rate demonstrated in cell Q15. The maximum capacity of each pile is 100,000. So what I want to do is when one pile reaches 100,000, start adding the material to the smallest of the 3 piles (which could be any of the 3 depending on the scenario).

Once I have done this I will look into altering the macro again to look at recovering material off these piles.

I hope this makes sense.

Cheers

John