Table Values used to auto calculate and return val

LeedMe 6 Posts Tuesday March 28, 2017Registration date April 12, 2018 Last seen - Apr 5, 2018 at 07:54 AM - Latest reply: TrowaD 2340 Posts Sunday September 12, 2010Registration dateModeratorStatus April 19, 2018 Last seen
- Apr 16, 2018 at 11:34 AM
Is there a way in a formula to have Excel continually look in a table, and insert a value in a cell , so that the value in another cell will never be below a value of 20? Once that is met, could it insert the P/N in a specified cell automatically?

Please see attached, and thank you in advance...
See more 

7 replies

Reply to this topic
TrowaD 2340 Posts Sunday September 12, 2010Registration dateModeratorStatus April 19, 2018 Last seen - Apr 5, 2018 at 11:28 AM
0
Helpful
4
Hi Leedme,

Shouldn't the result be part D because 100/5 = 20, so it is not below 20?

See how this code works out for you:
Sub RunMe()
Dim lRow, x As Integer

lRow = Range("K" & Rows.Count).End(xlUp).Row

For x = lRow To 2 Step -1
    If Range("C2").Value / Range("K" & x).Value >= 20 Then
        Range("B2").Value = Range("K" & x).Value
        Range("A2").Value = Range("J" & x).Value
        Exit Sub
    End If
Next x
End Sub 


The code will start looking at the bottom of your table. Check if the value in C2 divided by the table value is higher or the same as 20.
If not, then check the value above it.
If so, then copy the table value to B2 and the part number to the left to A2.


Best regards,
Trowa
Yes,
drastically simplified what I am really trying to do, so then I can see how the macro needs to created to do what is really needed.
I appreciate your help on this!!
Simplified it, but I think I left out an important detail.
I was hoping to have the macro/or formula not care about the math of C2/B2.
Can it just look at E2 and see its below the value of 20, then look in the table and insert the values into B2 until the value in E2 is not below 20?
I will manually be changing the value in C2
TrowaD 2340 Posts Sunday September 12, 2010Registration dateModeratorStatus April 19, 2018 Last seen - Apr 12, 2018 at 11:25 AM
Hi LeedMe,

The data in E2 is not linked to the table, so when E2 is below 20, then how do we know which data to pull from the table?

When you want to insert values in B2 until the value in E2 is not below 20, then that is what the provided macro does. I added the math C2/B2, so you can change the value in C2 and the macro will still work.

Could you re-explain what you enter manually and what you want to be done automatically?

Best regards,
Trowa
LeedMe 6 Posts Tuesday March 28, 2017Registration date April 12, 2018 Last seen - Apr 12, 2018 at 04:03 PM
I will attach what I am really working on so its clearer.
The value in E20 can not go below a set value of 6.
C2, C4, C5 and C6 are entered to satisfy the formulas in the picture.
I would like to have values from Column M automatically placed in A9, until the value in E20 is greater than 6.
When that is satisfied, then I would like which ever value was used from Column M, to insert the corresponding value in its row from Column L, into cell A3.
Respond to TrowaD
LeedMe 6 Posts Tuesday March 28, 2017Registration date April 12, 2018 Last seen - Apr 12, 2018 at 04:07 PM
0
Helpful
This is the sheet

Respond to LeedMe
TrowaD 2340 Posts Sunday September 12, 2010Registration dateModeratorStatus April 19, 2018 Last seen - Apr 16, 2018 at 11:34 AM
0
Helpful
Hi LeedMe,

So to get to the set value of 6 we can use the formula?:
Value from column M / C4 / C5 / A12 - C20

If that looks about right, then give the following code a try:
Sub RunMe()
Dim lRow, x As Integer

lRow = Range("M" & Rows.Count).End(xlUp).Row

For x = lRow To 9 Step -1
    If Range("M" & x).Value / Range("C4").Value / Range("C5").Value / _
    Range("A12").Value - Range("C20").Value >= 6 Then
        Range("A9").Value = Range("M" & x).Value
        Range("C3").Value = Range("L" & x).Value
        Exit Sub
    End If
Next x
End Sub


Curious to know if this works out for you.

Best regards,
Trowa
Respond to TrowaD