A common task in Excel is to define category names based on values; for example, grouping test scores into grades. A standard formula in Excel can do the job for a simple if then else scenario, but for multiple selections, the syntax can become complicated.

Let’s look at a simple set of test scores and the formula in the adjacent cell to define the result as passed or not achieved.
45 =if(a1>=50,”Passed”,”Not achieved”)

Excel And Nested If Formulas
The problem occurs when we need to assess values based on multiple ranges. In our example, if we wanted to assess a score of between 50 and 65 as “average” the following formula applies:
=if(and(a1>=50,a1

But this formula doesn’t allow for scores outside the “average” range such as the criteria for assessing scores below:
Below 50 = “Not achieved” 50-65= “Average” Over 65=”Above average”
The formula for applying the above conditions is complex:
=IF(A1=50,A165,”Above average”)))

Clearly, the nested formula can become cumbersome and difficult to debug so let’s look at an elegant VBA alternative.
A VBA Solution For Assessing Multiple Ranges

There are several different methods for assessing multiple values in VBA, but the one we’ll use is the select case method.

One of the best things you can do when writing VBA code is to reflect on the code and consider how easy it is to follow. First, we’ll select a column to assess and to make the code easier to read, assign the grade column and the number of scores to their own variables.

Range(“a1”).CurrentRegion.Columns(1).Select
items = Selection.Count
Now, we’ll loop through the selection and assign a grade to each score.
For x = 1 To items
test = (x)
‘ we don’t really need to reset the grade variable but it’s good practice to do so.
Case 0 To 49
grade = “Not achieved”Case 50 to 65
grade = “Average”Case 66 To 100