Eliminating Nested If statements

[jamesa580]

I'm trying to create an excel tool that will take the amount of product in process for my company at each stage of our process, and compare it to the amount of product we have need to ship. After this comparison is made, i would like the last column of my sheet to output the step in the process where the last piece for a shipment is located.

The following is an example I have put together:

<colgroup><col style="mso-width-source:userset;mso-width-alt:3145;width:65pt" width="86"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> <col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:2304;width:47pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> </colgroup><tbody>
[TD="class: xl65, width: 86"]PRODUCE[/TD]
[TD="class: xl65, width: 64"]On tree[/TD]
[TD="class: xl65, width: 70"]Harvested[/TD]
[TD="class: xl65, width: 64"]Cleaning[/TD]
[TD="class: xl65, width: 64"]Packed[/TD]
[TD="class: xl65, width: 63"]Cust. Qty[/TD]
[TD="class: xl65, width: 87"]RSK LOC[/TD]

[TD="class: xl65"]Apples[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]6[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]Harvested[/TD]

[TD="class: xl65"]Bananas[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]PLANT MORE[/TD]

[TD="class: xl65"]Oranges[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]36[/TD]
[TD="class: xl66"]PLANT MORE[/TD]

[TD="class: xl65"]Pears[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]On Tree[/TD]

[TD="class: xl65"]grapes[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]28[/TD]
[TD="class: xl66"]PLANT MORE[/TD]

[TD="class: xl65"]Cherries[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]17[/TD]
[TD="class: xl66"]Cleaning[/TD]

[TD="class: xl65"]Strawberries[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]PLANT MORE
[/TD]

[TD="class: xl65"]Nectarines[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]44[/TD]
[TD="class: xl66"]PLANT MORE
[/TD]

[TD="class: xl65"]Melons[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]6[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]On Tree
[/TD]

[TD="class: xl65"]Cantalope[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]38[/TD]
[TD="class: xl66"]PLANT MORE
[/TD]

</tbody>

Where the RSK LOC is what I am looking to output. My current equation is

=IF(E11>=F11, "Packed", IF((E11+D11)>=F11, "Cleaning", IF(SUM(C11:E11)>=F11, "Harvested", IF(SUM(B11:E11)>=F11, "On Tree", "PLANT MORE"))))

However the actual process is much longer than this with over 50 steps. Is there a quicker way to complete this function than my current approach?

 

[jamesa580]

Also, I just started getting the Invalid Qualifier error...

Here's my current code.

Sub RSK_LOC()

'define variables
    Dim i As Integer, j As Integer, Enough As Boolean
    Dim Rows As Integer, Cols As String
    Dim Orders As Integer

'Find the size of the table
    Cols = Sheets("active table").Cells(1, Cols.Count).End(xlToLeft).Column
    Rows = Range("A" & Rows.Count).End(xlUp).Row

'Work
For i = 2 To Rows 'Going down row by row
    Orders = Cells(i, Cols - 1).Value
    Enough = False
        For j = Cols - 2 To 2 Step -1 'Going backwards throughc columns?
            If j <> 3 Then
            If Orders > Cells(i, j).Value Then 'more orders left than at this stage
                Orders = Orders - Cells(i, j).Value
            Else 'this should have the last process with parts for a shipment
                Range("A1").Offset(i - 1, Cols - 1).Value = Cells(1, j).Value 'Should print stage in last colum
                Enough = True
            Exit For
        End If
    End If
    Next j
    If Enough = False Then Range("A1").Offset(i - 1, Cols - 1).Value = "Release More"
    End If
    Next i
End Sub

 

[j_harr]

Also, I just started getting the Invalid Qualifier error...

When you name variables in VBA, you have to be careful what name you give them. If you give them them the same name as something VBA already uses, it'll cause errors like the one you encountered. In this case, you named a variable Rows which is a pre-existing property, causing excel to throw the error.

Here's a working version of the code:

Sub RSK_LOC()
'define variables
    Dim i As Integer, j As Integer, Enough As Boolean
    Dim Rows_Count As Integer, Cols_Count As String
    Dim Orders As Integer
'Find the size of the table
    Cols_Count = Sheets("active table").Cells(1, Columns.Count).End(xlToLeft).Column
    Rows_Count = Range("A" & Rows.Count).End(xlUp).Row
'Work
For i = 2 To Rows_Count 'Going down row by row
    Orders = Cells(i, Cols_Count - 1).Value
    Enough = False
        For j = Cols_Count - 2 To 2 Step -1 'Going backwards through columns?
            If j <> 3 Then
            If Orders > Cells(i, j).Value Then 'more orders left than at this stage
                Orders = Orders - Cells(i, j).Value
            Else 'this should have the last process with parts for a shipment
                Range("A1").Offset(i - 1, Cols_Count - 1).Value = Cells(1, j).Value 'Should print stage in last colum
                Enough = True
            Exit For
        End If
    End If
    Next j
    If Enough = False Then
        Range("A1").Offset(i - 1, Cols_Count - 1).Value = "Release More"
    End If
    Next i
End Sub

I don't quite get what you're asking in your earlier post though, could you clarify what you want? If you're looking to use this for other tables, this code is set up to automatically scale itself to any table smaller than 32,768 rows / columns and will output the status in the last column.

 

[jamesa580]

J_harr

Thanks for the help, I have it all set up and the code is running perfectly.

 

[j_harr]

J_harr

Thanks for the help, I have it all set up and the code is running perfectly.

Glad to hear it's working for you.

 

[jamesa580]

One last question J,

I'm looking to add in a column after the last amount, that when the outcome is "Release More" I get the quantity as to how many more I need to release.

I've been playing with the code for a few days but haven't come close.

 

[j_harr]

I'm looking to add in a column after the last amount, that when the outcome is "Release More" I get the quantity as to how many more I need to release.

Sub RSK_LOC()
'define variables
    Dim i As Integer, j As Integer, Enough As Boolean
    Dim Rows_Count As Integer, Cols_Count As String
    Dim Orders As Integer, Count As Integer
'Find the size of the table
    Cols_Count = Sheets("active table").Cells(1, Columns.Count).End(xlToLeft).Column
    Rows_Count = Range("A" & Rows.Count).End(xlUp).Row
'Work
    For i = 2 To Rows_Count 'Going down row by row
        Orders = Cells(i, Cols_Count - 1).Value
        Count = 0
        Enough = False
        For j = Cols_Count - 2 To 2 Step -1 'Going backwards through columns
            If j <> 3 Then
                Count = Count + Cells(i, j).Value
                If Orders > Cells(i, j).Value Then 'more orders left than at this stage
                    Orders = Orders - Cells(i, j).Value
                Else 'this should have the last process with parts for a shipment
                    Range("A1").Offset(i - 1, Cols_Count - 1).Value = Cells(1, j).Value 'print stage in last colum
                    Enough = True
                    Exit For
                End If
            End If
        Next j
        If Enough = False Then  'not enough in process
            Range("A1").Offset(i - 1, Cols_Count - 1).Value = "Release More"
            Range("A1").Offset(i - 1, Cols_Count).Value = Cells(i, Cols_Count - 1).Value - Count    'print extra needed
        Else    'enough in process
            Range("A1").Offset(i - 1, Cols_Count).ClearContents
        End If
    Next i
End Sub

 

[j_harr]

I added in a variable named Count, it keeps a running total of the WIP. It adds the amount in every cell to itself to sum the WIP. At the end, if you need to release more, the program subtracts the existing count from the original order amount to calculate how many extra you need then prints that amount.