Upper & Lower limit of Nominal = Result VBA macro

linuxya

New Member
Joined
Mar 29, 2022
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
I would like to make the result to PASS or FAIL.
Example:
Nominal: 1.0
Upper Limit: 0.05
Lower Limit: 0.05
Result: 1.06
PASS/FAIL: FAIL (due to Result is over upper limit of Nominal: 1.06)
(Something like this as below)
2.jpg


Please help, thanks.
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
2,042
Office Version
  1. 2016
Without VBA:
H2:
Code:
=IF(AND(INDEX(G2>=(D2+E2:F2),)),"PASS","FAIL")
with VBA:
VBA Code:
Option Explicit
Sub test()
With Range("H2:H" & Cells(Rows.Count, "D").End(xlUp).Row)
    .Formula = "=IF(AND(INDEX(G2>=(D2+E2:F2),)),""PASS"",""FAIL"")"
    .Value = .Value
End With
End Sub
 
Upvote 0

linuxya

New Member
Joined
Mar 29, 2022
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Without VBA:
H2:
Code:
=IF(AND(INDEX(G2>=(D2+E2:F2),)),"PASS","FAIL")
with VBA:
VBA Code:
Option Explicit
Sub test()
With Range("H2:H" & Cells(Rows.Count, "D").End(xlUp).Row)
    .Formula = "=IF(AND(INDEX(G2>=(D2+E2:F2),)),""PASS"",""FAIL"")"
    .Value = .Value
End With
End Sub

It work wonderfully, thanks a lot!
 
Upvote 0

linuxya

New Member
Joined
Mar 29, 2022
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Without VBA:
H2:
Code:
=IF(AND(INDEX(G2>=(D2+E2:F2),)),"PASS","FAIL")
with VBA:
VBA Code:
Option Explicit
Sub test()
With Range("H2:H" & Cells(Rows.Count, "D").End(xlUp).Row)
    .Formula = "=IF(AND(INDEX(G2>=(D2+E2:F2),)),""PASS"",""FAIL"")"
    .Value = .Value
End With
End Sub

Hello, that's odd this happen... Is there any way to fix this, without having to switch around the "PASS/FAIL"? Thanks

1.jpg
 
Upvote 0

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
2,042
Office Version
  1. 2016
Sorry, should be:
Code:
=IF(AND(D2-G2>=F2,D2-G2<=E2),"PASS","FAIL")
 
Upvote 0

linuxya

New Member
Joined
Mar 29, 2022
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Sorry, should be:
Code:
=IF(AND(D2-G2>=F2,D2-G2<=E2),"PASS","FAIL")

Thanks, but sorry, with this code, the same thing happen as I mentioned at post #4, have to switch between "FAIL","PASS" or "PASS","FAIL"...
(I hope, there is way not to switch/change around the PASS/FAIL)
Incorrect:
Incorrrect.jpg


After change to "FAIL","PASS":
Correct.jpg
 
Upvote 0

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
2,042
Office Version
  1. 2016
Make sure "-0.05" in F2 is value, not text, like this:

Capture.JPG
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,540
Office Version
  1. 365
Platform
  1. Windows
For future reference.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Macro PASS or FAIL of Maximum/Minimum for a Nominal
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

Forum statistics

Threads
1,186,441
Messages
5,957,870
Members
438,329
Latest member
Aimon Rauf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top