I am currently working on a reliability calculator and as part of a wider calculation, the following loop is required:
'Add redundant units to any pieces of equipment with reliabilites lower than target
'Set Variables
DatRange = Worksheets(system).ListObjects("Table_" & system).ListColumns("Reliability of Configuration").DataBodyRange
minVal = Application.WorksheetFunction.Min(DatRange)
Do Until minVal > Target_Reliability
'Identify row of min value
Row = Application.WorksheetFunction.Match(minVal, DatRange, 0)
'Add redundancy to relevant row
quantity_fitted = Worksheets(system).ListObjects("Table_" & system).ListColumns("Quantity Fitted (n)").DataBodyRange(Row)
Worksheets(system).ListObjects("Table_" & system).ListColumns("Quantity Fitted (n)").DataBodyRange(Row) = quantity_fitted + 1
'Prime for next loop
DatRange = Worksheets(system).ListObjects("Table_" & system).ListColumns("Reliability of Configuration").DataBodyRange
minVal = Application.WorksheetFunction.Min(DatRange)
Loop
So each line/row of components is checked, by inspecting the reliability value stated in their "Reliability of Configuration" column, to find whichever line/row offers the lowest reliability. Then additional quantities are added to that line until it's reliability exceeds the target reliability of the system, which is set by the user separately.
This works in principle, except within another column I have a drop down selection for the user to input whether a particular component is arranged within a "Standby", "Active, or "Series" configuration.
What I need is the code above to run, but to omit any line/row in which the "Configuration:" column value is set to "Series".
In other words, is there a way to edit this part of the code:
'Set Variables
DatRange = Worksheets(system).ListObjects("Table_" & system).ListColumns("Reliability of Configuration").DataBodyRange
minVal = Application.WorksheetFunction.Min(DatRange)
Do Until minVal > Target_Reliability
So that instead of simply finding the minimum value, it finds the minimum value of any line which is also set to either "Active" or "Standby", ignoring any value of which the line is set to "Series"?
I hope this makes sense. I've been struggling with this for a while and really need help.
Thank you!