IF Then Else With 3 variables. Help please!

[olympus]

I am trying to write a series of IF then statements in VB for Excel. I have tried to use the:

IF [condition] Then
Statement
Else
End If

format without any success. I have three cells (F4, F10, and F14). Each of these cell values are controlled by radio buttons. F4 can have a value of 1, 2, 3, 4, 5, or 6. F10 can have a value of 1 or 2, and F14 can have a value of 1, 2,3,4,5,6, or 7. I want each each statement to depend on the possible combination of the values of these three cells. For example if F4=2, and F10=1 and F14=6 then the formula in cell g22 would be carried out, if F4=1, and F10=1, and F14=1, then the formula in cell g23 would be carried out and so on until all of the possible combinations for F4, F10 , and F14 are accounted for. Your help would be appreciated. Thanks.
John

 

[DPlank]

try this instead :

If [condition1] Or [condition2] Or [condition3] Then
    Statement
else
    Statement
End If

This is going to get tricky with the number of combinations, though.

What exactly are you trying to achieve? There may be a better way ...


Cheers,
Dave

Probably the only Test Analyst on Tek-Tips

animadverto vos in Abyssus!

Take a look at Forum1393 & sign up if you'd like

 

[JerryKlmns]

olympus,

How many combinations are these?????

Any way.... use folded Select Case ... End Select along with If.then...Else...EndIF

Select Case Cell("F14").Value 'this select is for F14
  Case 1   'First value of F14
    
    Select Case Cell("F4").Value  'this select is for F4
      Case 1   'First value of F4
        If Cell("F10").Value=1 then
          .
        Else
          .
        Endif
      Case 2   'Second value of F4

        same IF structure like case 1 for cell F4

      Case 3
        .
        .
      Case 6 'last value for F4
    End Select  'for cell F4

  Case 2   'Second value of F14

    same -Select Case & IF- structure like case 1 for F14

  Case 3
        .
        .
  Case 7 'last value for F14
End Select  'for cell F14

 

[SkipVought]

Hi,

you might want to consider the Select Statement. Concatenate the 3 values...

Select Case [F4] & [F10] & [F14]
  Case "111"

  Case "112"

  Case "113"
...
End Select

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[DPlank]

Either way, there are a lot of combinations to be considered if you want to get them all..

No super easy way to do this one I think. Probably best to use the Select Case method as described above.



Cheers,
Dave

Probably the only Test Analyst on Tek-Tips

animadverto vos in Abyssus!

Take a look at Forum1393 & sign up if you'd like

 

[pbrodsky]

That is a lot of choices. I like Skip's approach. However, if you turned the three selections into a 3-digit number, you might be able to use it directly as a row number.

It would help if you explained more about what you are trying to accomplish.

 

[olympus]

To pbrodsky:

In my Excel app the user selects one of six possible radio buttons for mg, mcg ,units, grams, etc. In the second section one picks one of two radio buttons for volume( ml or Liters) and in the third section the user selects one of 7 posible final doses (mcg/min, mg/min, units/hr, mcg/kg/min, etc.). I have then taken the three numbers generated from these radio boxes and used a series of if statements to perform the correct math. However things have gotten much too complicated, so I hoped to overcome this by writing some VB code. I also wondered if one could use a variation of VLOOKUP to accomplish this. Thanks. John

 

[pbrodsky]

What do the results and calculations look like?

It looks as though you really have just a few types of calculations with several different units. If that is true, you could probably rearrange things to select the units (a couple of vlookups?) and a simple if clause to pick the calculation.

 

[olympus]

to pbrodsky:
The selection of units does determinine the final calulation. The final result is a flow sheet sheet which shows the dose and its appropriate flow rate in ml/hr to set the pump at. The print area and the multiples of how the display is calulated (by 1, 10, or 100 increments) is specific to each agent. I was thinking that perhaps I could concatenate the three values and then perform a vlookup on this concatenated value. I have the Excel app working fine for when there were only 6 possible final dosing units, but am having difficulty adding a seventh. I have handled things up to this point by nested IF statements. Thanks.
John

 

[pbrodsky]

Concatenating the 3 values and doing a vlookup based on the concatenated value should work. You would need to make up a table with all the values and the information you need for the calculation (or the actual calculation). It shouldn't be too hard.

 

[mintjulep]

It seems like you are essentially attempting to do unit conversions, yes?

Then the form of your EQUATION is identical for every possible case. You just need to include the conversion factors as constants in the equation.

Each radio button assigns a particular value to one constant. Note that in some cases the constant may be 1 or 0.

The equation appears only once.

 

[byundt]

When I wrote some code to deal with user-selectable units of measure, I did something similar to mintjulep's suggestion. I wrote a function for each type of unit that supplied the conversion factor. Multiply by this number to go from user-specified units to standard units. Divide by this number to go from standard units to user-specified units.

For example, suppose the standard units for mass are mg. Your conversion factor function might be something like this:

Function MassUnits(sUnits As String, Optional sDrug As String)
Dim InternationalUnitsTable As Range
Set InternationalUnitsTable = Range("InternationalUnitsTable")
Select Case sUnits
Case "mcg"
    MassUnits = 0.01
Case "units"
    MassUnits = Application.VLookup(sDrug, InternationalUnitsTable, 2, False)
Case "grams"
    MassUnits = 1000
Case Else   'Default unit is mg
    MassUnits = 1
End Select
End Function

Note the use of a VLOOKUP formula using a worksheet table to convert from international units to mg. This makes the code easier to update than hard-coding the table in the function. Especially if you use dynamic named ranges to define range for the lookup table.

If you had similar functions for VolumeUnits and DoseUnits, then you could use a worksheet formula like:
=865*A1*MassUnits(MassUnit)*A2*VolumeUnits(VolumeUnit/DoseUnits(DoseUnit)
This formula assumes the existance of three named ranges which contain the user's choice of mass unit, volume unit and dose unit. I'd put these in a data validation dropdown. The 865 is an arbitrary number that makes the calculation work out when the user selects standard units for everything.

Brad