0

I am currently working on a reliability calculator and as part of a wider calculation, a loop is required where 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.

Within another column titled "Redundancy Configuration:" 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".

I have managed to capture this in an excel formula below:

=MINIFS(Table_System[Reliability of Configuration],Table_System[Redundancy Configuration:],"<>Series")

However, I cannot get my VBA code attempt to run without error. Here is my best attempt so far:

'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
    DatRange2 = Worksheets(system).ListObjects("Table_" & system).ListColumns("Redundancy Configuration:").DataBodyRange
    'minVal = Application.WorksheetFunction.Min(DatRange)
    minVal = Application.WorksheetFunction.MinIfs(DatRange, DatRange2, "<>Series")

    Do Until minVal > Target_Reliability

The commented out "minVal" would correctly identify the minimum value within the "Reliability of Configuration" column. But I can't get the MinIfs code to work to identify the correct minimum value, omitting series selection.

Please help!

braX
  • 11,506
  • 5
  • 20
  • 33
T. Coates
  • 19
  • 8
  • new in Office 365 and 2019 – Vincent G Mar 02 '20 at 15:32
  • 3
    You need `Set ` at the start of the two lines that determine the ranges. – Rory Mar 02 '20 at 15:35
  • Your `MinIfs` equation is fine. The issue is prob with the variables or ranges. Print your ranges to immediate window and see if the ranges are what you expected (`DatRange & DatRange2`). Update, @Rory called it already :) – urdearboy Mar 02 '20 at 15:36
  • @urdearboy no that doesn't seem to work unfortunately – T. Coates Mar 02 '20 at 15:37
  • 1
    What are `DatRange` & `DatRange2` declared as? Are they `Ranges`? If so, you have to `Set` ranges as previously stated. If they are not ranges then you are putting in the wrong variables into the `MinIfs` argument. The syntax is `MinIfs( Range1, Range2, Criteria)` so your first two arguments must be ranges – urdearboy Mar 02 '20 at 15:38
  • @Rory apologies I'm still fairly new to VBA, could you elaborate on what you mean? – T. Coates Mar 02 '20 at 15:38
  • @urdearboy thank you that makes sense :) I'm now looking up how to set 2 ranges within VBA, and hopefully that will have it – T. Coates Mar 02 '20 at 15:41
  • 2
    I mean: `Set DatRange = ...` rather than just `DatRange = ...` and the same for the other range. – Rory Mar 02 '20 at 15:44
  • `Objects` must be set and `Variables` must be defined. `Set Object = Something` vs `Variable = 10` – urdearboy Mar 02 '20 at 15:49
  • @Rory I've tried to simply put "Set" in front of the DatRange and DatRange2 code as you suggested but I still can't get it working. Thank you for giving me an avenue to pursue, I'll keep looking :) – T. Coates Mar 02 '20 at 15:54
  • What type of variable is `system`? It is highly recommended that you don't use reserved words as variable names (i.e. system). After doing what @Rory suggested, are `DatRange` and `DatRange2` set to anything? (you can check in the watch window if they are set as a `Range` object) – Zac Mar 02 '20 at 16:13
  • Also, what is happening currently? Another error? – Rory Mar 02 '20 at 22:26
  • @Rory Apologies for the late reply, I continued working on this throughout the evening and have now got the code working! Your suggestion was correct, it turned out the original problem repeated itself further down the code, which I have now corrected. Thank you for all your help! – T. Coates Mar 03 '20 at 08:43

0 Answers0