Select Case help (vs IF statement)

[caho333]

I currently have a spreadsheet that has a nested IF statement to determine some quantities, and I think it may be better to use the Select Case instead of what I have, but I'm a little unsure as how to go about that. Here's my current forumla:

=IF(F2="lbs",SUM(B2*E2)/0.7,IF(F2="ea",SUM(B2*E2),IF(F2="sqft",SUM(B2*E2)/0.7,IF((AND(F2="in",H2="ft")),ROUNDUP(SUM(B2*E2)/12,0),ROUNDUP(SUM(B2*E2,1),ROUNDUP(E2,0))))))


As of now, I have an unknown number of items that will be on this worksheet. The number will vary each time someone does a new export of this data to the spreadsheet. It could be 7 items, could be 25, could be 100. What I need to be able to do is find the value in column F on my spreadsheet, and then perform the calculations as shown in the formula above based off of what that value is (it will either be lbs, ea, in, sqft, or ft). To throw another wrinkle into it, the one calculation takes into account both column F and column H, in order to do a translation from inches to ft. So, can I use Select Case with a range of the entire column F to do this, as I do not know how many items will be in the spreadsheet. I can get the basics of the Select Case, but I'm unsure of the best way to go about it for the entire range. For example, if I was doing just row two in my spreadsheet I would take it as:

Select Case Range("F2")
Case lbs
Range("G2") = ("B2" * "E2") / 0.7
Case ea
Range("G2") = ("B2 * "E2")
Case sqft
Range("G2") = ("B2" * "E2") / 0.7
Case Else
Range("G2") = " "
End Select


The other thing I'm unsure of in the Select Case statement would the how to go about finding when column F is inches (in) and column H is feet (ft). Do I need a separate statement for that? Thanks for any help.

 

[Joe4]

Welcome to the Board!

A few tips.

When checking for text entries using "Select Case" statements, you will need quote around the text entries, i.e.

Case "lbs"
...

If you need to do this on an undetermined number of rows of data, you can use a loop to do so. First locate the last row of date, then do the loop, i.e.

Dim myLastRow as Long
Dim myRow as Long

' Find last row of data in column F
    myLastRow=Cells(Rows.Count,"F").End(xlUp).Row

' Loop through column F from row 2 to the last row
    For myRow = 2 to myLastRow
'       perform your Select Case statements here
        ....
    Next myRow

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Two ways to use VBA for this are:
Make a macro that that reads the number of rows and calculates the result for each row a places it in a specified column (lets say Column J).
Make a User Defined Function (UDF) that allows you to place a formula in a cell that does the calculation in VBA and returns the result to the cell.

Your UDF might have parameters for each of the inputs...
Entered in Cell J2: =MyQtyCalc(B2,E2,F2,H2)

or it could simply be coded to look in those specified columns of the same row
Entered in Cell J2: =MyQtyCalc()

If you'd like some help with either of those approaches, please clarify...

1. What do each of those columns represent (this will allow the variables to be named in a meaningful way that makes the code easier to follow).

2. Do you ever have the condition that F is inches and H is not feet?

3. I presume column G is the number of feet. If so, why isn't that being used in the calculation?

4. The last part of the formula you are trying to emulate is unusual and perhaps there is an error there.
ROUNDUP(SUM(B2*E2,1),ROUNDUP(E2,0))))))
This appears to be saying that you want to use the result of ROUNDUP(E2,0) to be the number of digits to round up SUM(B2*E2,1)
Is that the intent?

 

[caho333]

Jerry - thanks for the reply. Maybe my original question should have been should I be using VBA for this, I guess. I'm thinking that it would be easier than what was in place. Basically, what happened was a user built a spreadsheet to calculate a total bill of materials, and used that formula in the original post to calculate the total quantities that would be needed for the items on this bill of materials. So, they basically just used the formula and copied it down the column, but that messes things up when there's either nothing in the rows (if copied to the entire column) or when the number of rows changes (the new rows do not have the formula). I'll answer your questions in reverse order actually.... Column G is what we're trying to find in this instance. It's going to be the total number of lbs, sqft, ft, or ea, and I need to get that value by performing the calculation as needed from the formula. Currently, the formula in the original post is applied to the cells in column G. The columns being multiplied are the number of items and the quantity (again either lbs, sqft, in, or ea) representing a single of that item (thus multiplying them together to get the total needed). And the other numbers in the equation or number that are in there to account for waste material. And the intent is to round up the items that need to be rounded off, as we would not want to round down to less material. So my intent is to try find a better way to do this, than the formula that they originally came up with, and that's why I was looking at VBA and the Select Case. I don't know if that clears anything up?

Hi and Welcome to the Board,

Two ways to use VBA for this are:
Make a macro that that reads the number of rows and calculates the result for each row a places it in a specified column (lets say Column J).
Make a User Defined Function (UDF) that allows you to place a formula in a cell that does the calculation in VBA and returns the result to the cell.

Your UDF might have parameters for each of the inputs...
Entered in Cell J2: =MyQtyCalc(B2,E2,F2,H2)

or it could simply be coded to look in those specified columns of the same row
Entered in Cell J2: =MyQtyCalc()

If you'd like some help with either of those approaches, please clarify...

1. What do each of those columns represent (this will allow the variables to be named in a meaningful way that makes the code easier to follow).

2. Do you ever have the condition that F is inches and H is not feet?

3. I presume column G is the number of feet. If so, why isn't that being used in the calculation?

4. The last part of the formula you are trying to emulate is unusual and perhaps there is an error there.
ROUNDUP(SUM(B2*E2,1),ROUNDUP(E2,0))))))
This appears to be saying that you want to use the result of ROUNDUP(E2,0) to be the number of digits to round up SUM(B2*E2,1)
Is that the intent?

 

[Jerry Sullivan]

Maybe my original question should have been should I be using VBA for this, I guess. I'm thinking that it would be easier than what was in place.

That's a great comment. Either formulas or VBA could work for the problem you describe and it really comes down to which method works better for you.

The calculations are fairly simple, so speed of execution isn't a factor. The main criteria is probably ease of use, clarity and maintenance (if/when changes are required).

Below are some VBA examples that you can try out to see which works best for you - and whether any of them work better than a formula.
There is an example for each of the 3 options that Joe and I mentioned.

Building on Joe's code suggestion, here is a macro that would process all the rows and place results in Column G.

Sub CalcAllMaterial()
    Dim myLastRow As Long, myRow As Long, NbrItems As Long
    Dim Qty As Double
    Dim Units As String, ConvertInchesTo As String
    Dim vResult As Variant
    Dim ws As Worksheet
    
    Set ws = Sheets("Sheet1")
' Find last row of data in column F
    myLastRow = ws.Cells(Rows.Count, "F").End(xlUp).Row

' Loop through column F from row 2 to the last row
    For myRow = 2 To myLastRow
' Read data for this row
        With ws
            NbrItems = .Cells(myRow, "B")
            Qty = .Cells(myRow, "E")
            Units = .Cells(myRow, "F")
            ConvertInchesTo = .Cells(myRow, "H")
        End With
        
'       perform your Select Case statements here
        Select Case LCase$(Units)
            Case "lbs", "sqft"
                vResult = (NbrItems * Qty) / 0.7
            Case "ea"
                vResult = (NbrItems * Qty)
            Case "in"
                vResult = IIf(LCase$(ConvertInchesTo) = "ft", _
                    Application.RoundUp(NbrItems * Qty / 12, 0), _
                    Application.RoundUp(NbrItems * Qty, 1))
            Case Else
                vResult = ""
        End Select
        ws.Cells(myRow, "G") = vResult
    Next myRow
    
End Sub

Here is a UDF that takes parameters for each of the inputs.
A formula entered in Cell G2 could be:
=CalcMaterial(B2,E2,F2,H2)
You would copy this formula down Column G just like a worksheet formula.

Public Function CalcMaterial(NbrItems As Long, Qty As Double, _
        Units As String, ConvertInchesTo As String) As Variant

    On Error GoTo ErrHandler
    
    Select Case LCase$(Units)
        Case "lbs", "sqft"
            CalcMaterial = (NbrItems * Qty) / 0.7
        Case "ea"
            CalcMaterial = (NbrItems * Qty)
        Case "in"
            CalcMaterial = IIf(LCase$(ConvertInchesTo) = "ft", _
                Application.RoundUp(NbrItems * Qty / 12, 0), _
                Application.RoundUp(NbrItems * Qty, 1))
        Case Else
            CalcMaterial = ""
    End Select
    Exit Function
ErrHandler:
    CalcMaterial = CVErr(xlValue)
End Function

Lastly here is a UDF that takes no parameters. This simplifies things in the Worksheet, but it means that you need to change the UDF code if you rearrange your columns.
A formula in G2 could be:
=CalcMaterial2()

Public Function CalcMaterial2() As Variant
    Dim NbrItems As Long, lRow As Long
    Dim Qty As Double
    Dim Units As String, ConvertInchesTo As String
    
    On Error GoTo ErrorValue
    If TypeName(Application.Caller) = "Range" Then
        lRow = Application.Caller.Row
        With Application.Caller.Parent
            NbrItems = .Cells(lRow, "B")
            Qty = .Cells(lRow, "E")
            Units = .Cells(lRow, "F")
            ConvertInchesTo = .Cells(lRow, "H")
        End With
            
        Select Case LCase$(Units)
            Case "lbs", "sqft"
                CalcMaterial2 = (NbrItems * Qty) / 0.7
            Case "ea"
                CalcMaterial2 = (NbrItems * Qty)
            Case "in"
                CalcMaterial2 = IIf(LCase$(ConvertInchesTo) = "ft", _
                    Application.RoundUp(NbrItems * Qty / 12, 0), _
                    Application.RoundUp(NbrItems * Qty, 1))
            Case Else
                CalcMaterial2 = ""
        End Select
        Exit Function
    End If
ErrorValue:
    CalcMaterial2 = CVErr(xlValue)
End Function

I still think the formula you are using is not modeling the RoundUp exactly as you intend.
The calculations in all these VBA examples will give a different result than your formula for cases in which Col F= "in" and Col H is not "ft". Hopefully this gives you a good base with which to experiment.

 

[caho333]

Ok...that was very helpful, so thanks for that. I have one other question, and wanted to bounce it off of someone with a little more Excel experience than I have. After I run the calculations through, and get the current values, I then want to group and sub-total like items, which is going to be based off an inventory id value (which is one of the other columns on the spreadsheet). Is it better to do that through code/macros? Or would it better to use a pivot table or something? Or would either of those work and it would basically be a matter of preference? I've not worked with pivot tables that much, so I'm not exactly sure how they work with the changing range of data (i.e.: the current export in the spreadsheet might be cells A1:H8, but the next export might be A1:H28. I'll do a little playing around with it, but wondered if anyone with experience with something like that had any suggestions on preferred methods. Thanks.

 

[Jerry Sullivan]

PivotTables would generally be a good match for what you describe- it would depend on whether or not you need some feature or calculation that can't be accomplished with a PivotTable.

Regarding the problem of varying number of rows in the data source, that can be addressed by defining a dynamic named range for your data and using that name as the data source reference instead of a static reference to a fixed range address.