Table Values used to auto calculate and return val
Closed
LeedMe
Posts
5
Registration date
Tuesday March 28, 2017
Status
Member
Last seen
April 12, 2018
-
Apr 5, 2018 at 07:54 AM
Blocked Profile - Apr 24, 2018 at 04:33 PM
Blocked Profile - Apr 24, 2018 at 04:33 PM
Related:
- Table Values used to auto calculate and return val
- Grand theft auto 1 download - Download - Action and adventure
- How to stop facebook auto refresh - Guide
- Grand theft auto v free download no verification for pc - Download - Action and adventure
- Nvidia drivers auto detect - Guide
- School time table software free download full version - Download - Organisation and teamwork
4 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 5, 2018 at 11:28 AM
Apr 5, 2018 at 11:28 AM
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:
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
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
LeedMe
Posts
5
Registration date
Tuesday March 28, 2017
Status
Member
Last seen
April 12, 2018
Apr 12, 2018 at 04:07 PM
Apr 12, 2018 at 04:07 PM
This is the sheet
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 16, 2018 at 11:34 AM
Apr 16, 2018 at 11:34 AM
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:
Curious to know if this works out for you.
Best regards,
Trowa
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 24, 2018 at 11:53 AM
Apr 24, 2018 at 11:53 AM
@ Mark
This site is a great source of Excel issues to keep my skills up. And when the scope changes, new challenges arise. Without it my skills would degenerate and by the time I need an Excel solution myself, most of the stuff I learned, would have been forgotten.
And isn't it a great thing to offer a solution for free? Not all people coming here are interested in becoming provicient in Excel, just to solve their issue and be done with it.
All that is nice to get in return is a some gratitude, some appreciation. Which I'm getting from this poster, so I'm happy to help.
This site is a great source of Excel issues to keep my skills up. And when the scope changes, new challenges arise. Without it my skills would degenerate and by the time I need an Excel solution myself, most of the stuff I learned, would have been forgotten.
And isn't it a great thing to offer a solution for free? Not all people coming here are interested in becoming provicient in Excel, just to solve their issue and be done with it.
All that is nice to get in return is a some gratitude, some appreciation. Which I'm getting from this poster, so I'm happy to help.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 24, 2018 at 11:57 AM
Apr 24, 2018 at 11:57 AM
Hi LeedMe,
Then we need to unhide the rows before the code and hide it after the code.
So go ahead hide rows 7 to 21 and give this adjusted code a try:
Best regards,
Trowa
Then we need to unhide the rows before the code and hide it after the code.
So go ahead hide rows 7 to 21 and give this adjusted code a try:
Sub RunMe()
Dim lRow, x As Integer
Rows("7:21").EntireRow.Hidden = False
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
Rows("7:21").EntireRow.Hidden = True
Exit Sub
End If
Next x
Rows("7:21").EntireRow.Hidden = True
End Sub
Best regards,
Trowa
Apr 11, 2018 at 08:24 AM
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!!
Updated on Apr 11, 2018 at 12:23 PM
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
Apr 12, 2018 at 11:25 AM
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
Apr 12, 2018 at 04:03 PM
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.