An Excel “if” assertion allows the worksheet to vary the worth of a cell based mostly on one other cell. For instance, when you had these check outcomes, the worksheet would put a “move” or “fail” within the cell containing the formulation.
fifty five,=if(a1>=50,"Pass","Fail")
forty five,=if(a2<50,"Fail","Pass")turns into:
fifty five,Pass
forty five,Fail
The formulation turn out to be difficult when extra parameters are included and this text will present you learn how to use VBA to write down the “if” assertion.
Handling Complex If Formulas
A drawback can happen when the assertion accommodates a number of values with AND OR parameters; in case you have been making an attempt to grade the scores above slightly than a easy “cross” or “fail” the components turns into extra difficult.
Here’s the formulation to grade the scores into “Fail”, “Pass” Or “A”; the place a rating of 70 or higher was an “A”.
=IF(AND(A1>zero,A1<50),"Fail",if(and(a1>=50,a1<70),"Pass",if(and(a1>=70,a1<=one hundred),"A",)))
For an evidence of how you can write “if” formulation containing a number of “AND” values, you’ll be able to Google: “Excel if and statements”.
You can see that complicated Excel formulation usually are not straightforward to comply with and do not use a logical syntax. That’s as a result of they must be written on a single line. Fortunately, you need to use VBA to write down the method in plain English after which use a couple of strains of code to create the if assertion.
First, let’s write the method in plain English into A separate cells, with the outcome within the subsequent cell.
if a1>zero and a1<50,"Fail"
if a1>=50 and a1<70,"Pass"
if a1>=70 and a1<=one hundred,"A"
We now use VBA code to loop by way of the cells and parse the values into the textual content string we’d like for the “if” components. We need every of the statements to be transformed into a normal syntax, for instance, line M ought to turn out to be:
if(and(a1>zero,a1<50),"Fail",subsequent assertion)
First, we’ll choose the vary. Note that we use the chr(34) character to make use of as citation marks.
Range("a1").SelectSet rng = Range("a1:" & ActiveCell.End(xlDown).Address)
quot=chr(34)
myStr="="
Next, we’ll loop via every cell and convert the plain English code into the Excel components we’d like.
For i = B To rng.Rows.Count
myStr = myStr & "if(and(" & Replace(rng.Rows(i), "and", ",")
myStr = myStr & ")," & quot & rng.Rows(i).Offset(zero, M) & quot & ","
Next
For i = M To rng.Rows.Count
myStr = myStr & ")"
Next
The code above produces the next string which may now be inserted into the suitable cells to create the right outcome for the components.
=IF(AND(A1>zero, A1<50),"Fail",IF(AND(A1>=50, A1<75),"Pass",IF(AND(A1>=seventy five, A1<=one hundred),"A",)))
The formulation may be manually entered within the cells, or utilizing VBA just like the code under.
Range("d2").Select
ActiveCell.Formula = myStr
Set rng = ActiveCell.CurrentRegion.Columns(B)
Selection.AutoFill Destination:=Range("d2:d" & rng.Rows.Count)
Once you are capable of write out what you want in plain English, the VBA code takes care of the remaining, with out you needing to know the complicated components.
Summary
This code snippet is an effective instance of a process you should use without having to know the way it works. Excel formulation could be difficult, however the VBA process might be edited to work with the present challenge you’re engaged on.
