Programming codex

How To Use VBA To Create A Complex If Statement In Excel

How To Use VBA To Create A Complex If Statement In Excel

[ad_1]

An Excel “if” statement enables the worksheet to change the value of a cell-based on another cell. For example, if you had these test results, the worksheet would put a “pass” or “fail” in the cell containing the formula.

  55, = if (a1> = 50, "Pass", "Fail") 
  45, = if (a2 <50, "Fail", "Pass")

becomes:

55, Pass
45, Fail

The formulas become challenging when more parameters are included and this article will show you how to use VBA to write the “if” statement.

Handling Complex If Formulas

A problem can occur when the statement contains multiple values ​​with AND OR parameters; if you were trying to grade the scores above rather than a simple “pass” or “fail” the formula becomes more complicated.

Here’s the formula to grade the scores into “Fail”, “Pass” Or “A”; where a score of 70 or greater was an “A”.

  = IF (AND (A1> 0, A1 <50), "Fail", if (and (a1> = 50, a1 <70), "Pass", if (and (a1> = 70, a1 <= 100) , "A",)))

For an explanation of how to write “if” formulas containing multiple “AND” values, you can Google: “Excel if and statements”.

You can see that complex Excel formula are not easy to follow and don’t use a logical syntax. That’s because they need to be written on a single line. Fortunately, you can use VBA to write the formula in plain English and then use a few lines of code to create the if statement.

First, let’s write the formula in plain English into 3 separate cells, with the result in the next cell.

  if a1> 0 and a1 <50, "Fail" 
  if a1> = 50 and a1 <70, "Pass" 
  if a1> = 70 and a1 <= 100, "A"

We now use VBA code to loop through the cells and parse the values ​​into the text string we need for the “if” formula. We want each of the statements to be converted into a standard syntax, for example, line 1 should become:

  if (and (a1> 0, a1 <50), "Fail", next statement )

First, we’ll select the range. Note that we use the Chr (34) character to use as quotation marks.

  Range ("a1"). Select

Set rng = Range (“a1:” & ActiveCell.End (xlDown) .Address)

quot = chr (34)

myStr = “=”

Next, we’ll loop through each cell and convert the plain English code into the Excel formula we need.

For i = 1 To rng.Rows.Count

myStr = myStr & “if (and (” & Replace (rng.Rows (i), “and”, “,”)

myStr = myStr & “),” & quot & rng.Rows (i) .Offset (0, 1) & quot & “,”

Next

For i = 1 To rng.Rows.Count

myStr = myStr & “)”

Next

The code above produces the following string which can now be inserted into the appropriate cells to create the correct result for the formula.

  = IF (AND (A1> 0, A1 <50), "Fail", IF (AND (A1> = 50, A1 <75), "Pass", IF (AND (A1> = 75, A1 <= 100) , "A",)))

The formula can be manually entered in the cells or using VBA like the code below.

  Range ("d2"). Select 
  ActiveCell.Formula = myStr 
  Set rng = ActiveCell.CurrentRegion.Columns (1) 
  Selection.AutoFill Destination: = Range ("d2: d" & rng.Rows.Count)

Once you’re able to write out what you need in plain English, the VBA code takes care of the rest, without you needing to understand the complex formula.

Summary

This code snippet is a good example of a procedure you can use without needing to understand how it works. Excel formulas can be challenging, but the VBA procedure can be edited to work with the current project you are working on.

[ad_2]

Source by Andy L Gibson

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on whatsapp

Leave a Reply

Your email address will not be published. Required fields are marked *

Search:

Topics

Recent Posts