VBA Excel; Stumped on formula to variable measurements to fixed measurements

I'm trying to create an Excel VBA formula as follows:

I have a count of bolts that are always the same length on a given run.
Individual runs may contain alternate lengths, but the lengths will be identical for each run.

Example: Run 1 = qty 520 bolts at 25" each
Example: Run 2 = Qty 100 bolts at 14" each
Example: Run 3 = Qty 100 bolts at 22" each

I have two variables:
BoltQty
BoltLength

I need a formula that will calculate how many pieces of 96" threaded rod that I need to purchase for each run.

So for example, on Run #1, I need to figure out how many pieces of 96" threaded rod I will need to obtain to cut all qty 500, 25" long bolts
And on Run #2, I need to figure out how many pieces of 96" threaded rod I will need to obtain to cut all qty 100, 14" long bolts

The formula only needs to work per individual run, it does not have to cover any other run.

Any help would be appreciated; I'm hoping that someone might have a default formula that they use for situations like this

You need to use integer division to find bolts per rod:
96/25 = 3.84 ==> 3 Round down. Throw away the remainder. Yes this is a lot of waste.
96/14 = 6
96/22 = 4

You need normal division and round up for number of rods:
520/3 = 173.3 ==> 174 and so on

Using your first example (qty 520 bolts at 25" each) you'd need
520 * 25 = 13,000 inches
13,000 / 96 = 135.416...
Rounded up = 136 rods

0

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Maybe I'm missing something but the formula doesn't work for me:

((Qty * Length) /96))

Using this example:

Qty 8 bolt sections @ 49" length each

RodsNeeded = ((8 * 49) / 96)

8 * 49 = 392

392/96 = 4.083

However, as I see it, this is incorrect. Since the length of the bolt is greater than 1/2 of the 96" threaded rod, (49 x 2 = 98) I would only be able to get qty 04 bolts (as opposed to 08 needed) out of the 04 rods total generated by the formula.

Function RodsNeeded(rQty As Range, rLen As Range) As Integer Dim intBoltsPerRod As Integer intBoltsPerRod = 96 \ rLen RodsNeeded = rQty * intBoltsPerRodEnd Function

Rounding up will, I assume just bring me up to the next sequential rod which in tis case is "5" The formula should generate 8 rods using the above example

I am using straight up VBA in a userform---no cells involved, only variables---so I was unable to test "Roundup" & "RoundDown" I will check for the equivalent VBA functions and test d-glitch's formula

0

Member_2_7966101Author Commented:

Rounding up will, I assume just bring me up to the next sequential rod which in tis case is "5" The formula should generate 8 rods using the above example

I am using straight up VBA in a userform---no cells involved, only variables---so I was unable to test "Roundup" & "RoundDown" I will check for the equivalent VBA functions and test d-glitch's formula

Thank you all. I have to leave my workstation. FYI: In my testing of d-glitch's formulas I'm getting 16 rods as opposed to the needed 8. I will follow up here tomorrow.

96/25 = 3.84 ==> 3 Round down. Throw away the remainder. Yes this is a lot of waste.

96/14 = 6

96/22 = 4

You need normal division and round up for number of rods:

520/3 = 173.3 ==> 174 and so on