While working with data sets in **VBA** in Excel, we often need to **round a single value or a set of values to the nearest 5**. In this article, I’ll show you how you can **round a value to the nearest 5** using **VBA** in Excel.

Excel VBA: Round to Nearest 5 (Quick View)

```
Sub Round_to_Closer_Nearest_5()
SheetName = "Sheet1"
DataSet = "C4:C13"
Output = "D4:D13"
Set Input_Range = Worksheets(SheetName).Range(DataSet)
Set Output_Range = Worksheets(SheetName).Range(Output)
For i = 1 To Input_Range.Rows.Count
For j = 1 To Input_Range.Columns.Count
Number = Input_Range.Cells(i, j)
If Int(Number / 5) = (Number / 5) Then
Nearest_5 = Number
Else
k = 0
While k < Number
k = k + 5
Wend
If (k - Number) > (5 / 2) Then
Nearest_5 = k - 5
Else
Nearest_5 = k
End If
End If
Output_Range.Cells(i, j) = Nearest_5
Next j
Next i
End Sub
```

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**An Overview of the VBA Code to Round a Number to the Nearest 5 (Step-by-Step Analysis)**

So, without further delay, let’s go to our main discussion today. We’ll break down the **VBA** code step-by-step to learn how to **round a number to the nearest 5** using Excel **VBA**.

**⧪ Step 1: Taking the Input Number**

The first step is simple. We have to take the number that’ll we round as the input.

For the sake of this example, let’s take **238.87**.

`Number = 238.87`

**⧪ Step 2: Checking whether the Number is Divisible by 5 or Not.**

Next, we’ve to check whether the number is visible by **5** or not. Because if it’s visible, we needn’t do anything. We’ll return the original number.

We’ll use the **Int function** of **VBA** here.

```
If Int(Number / 5) = (Number / 5) Then
Nearest_5 = Number
End If
```

**⧪ Step 3: Iterating through a While – loop to Round to the Nearest 5**

Now, if the number is not divisible by **5**, we’ll iterate through a **while loop** to round it to the nearest **5**.

But one thing to remember, the output returned here will be the upper nearest to 5, not the lower nearest.

```
Else
i = 0
While i < Number
i = i + 5
Wend
Nearest_5 = i
```

**⧪ Step 4 (Optional): Converting from the Upper Nearest Value to the Lower Nearest**

The output returned by the while loop was the upper nearest one. Now, if you want, you can convert this by replacing the line **Nearest_5 = i **by this:

` Nearest_5 = i - 5`

**⧪ Step 5 (Optional): Converting to the Closer Nearest**

We’ve shown how you can convert a number to both the upper nearest and the lower nearest 5. But if you can convert it to the one which is closer, you can again replace the line **Nearest_5 = i – 5 **with this:

```
If (i - Number) > (5 / 2) Then
Nearest_5 = i - 5
Else
Nearest_5 = i
End If
```

**Developing 3 Macros to Round a Range of Values to the Nearest 5 Using Excel VBA**

Here we’ve got a data set in the range **B4:C13** of a worksheet called **Sheet1** in a workbook that contains the names of some students and their average marks in an examination in a school.

And there is another column **D4:D13** left open for converting the marks to the nearest 5’s.

Now, we’ll develop 3 different **Macros** to round the marks to the nearest 5.

- One to the upper nearest 5
- One to the lower nearest 5
- And one to the one which is closer.

**1. Round to Upper Nearest 5 Using Excel VBA**

First of all, we’ll develop a **Macro** to round all the marks to the upper nearest 5’s. The **VBA** code will be:

⧭** VBA Code:**

```
Sub Round_to_Upper_Nearest_5()
SheetName = "Sheet1"
DataSet = "C4:C13"
Output = "D4:D13"
Set Input_Range = Worksheets(SheetName).Range(DataSet)
Set Output_Range = Worksheets(SheetName).Range(Output)
For i = 1 To Input_Range.Rows.Count
For j = 1 To Input_Range.Columns.Count
Number = Input_Range.Cells(i, j)
If Int(Number / 5) = (Number / 5) Then
Nearest_5 = Number
Else
k = 0
While k < Number
k = k + 5
Wend
Nearest_5 = k
End If
Output_Range.Cells(i, j) = Nearest_5
Next j
Next i
End Sub
```

⧭** Output:**

Run the code. It’ll convert the marks in range **C4:C13** of **Sheet1** to the upper nearest 5’s in range **D4:D13** of the worksheet.

⧭** Notes:**

The first 3 lines of the code contain the inputs to the code (**SheetName, DataSet,** and **Output**). Don’t forget to change them according to your needs.

**Read More: How to Round to Nearest 10 Cents in Excel (4 Suitable Methods)**

**Similar Readings**

**How to Round Excel Data to Make Summations Correct (7 Easy Methods)****Stop Excel from Rounding Large Numbers (3 Easy Methods)****How to Roundup a Formula Result in Excel (4 Easy Methods)**

**2. Round to Lower Nearest 5 Using Excel VBA**

First of all, we’ll develop a **Macro** to round all the marks to the lower nearest 5’s. The **VBA** code will be:

⧭** VBA Code:**

```
Sub Round_to_Lower_Nearest_5()
SheetName = "Sheet1"
DataSet = "C4:C13"
Output = "D4:D13"
Set Input_Range = Worksheets(SheetName).Range(DataSet)
Set Output_Range = Worksheets(SheetName).Range(Output)
For i = 1 To Input_Range.Rows.Count
For j = 1 To Input_Range.Columns.Count
Number = Input_Range.Cells(i, j)
If Int(Number / 5) = (Number / 5) Then
Nearest_5 = Number
Else
k = 0
While k < Number
k = k + 5
Wend
Nearest_5 = k - 5
End If
Output_Range.Cells(i, j) = Nearest_5
Next j
Next i
End Sub
```

⧭** Output:**

Run the code. It’ll convert the marks in range **C4:C13** of **Sheet1** to the lower nearest 5’s in range **D4:D13** of the worksheet.

⧭** Notes:**

Again, the first 3 lines of the code contain the inputs to the code (**SheetName, DataSet,** and **Output**). Don’t forget to change them according to your needs.

**Read More: Rounding Time in Excel to Nearest Hour (6 Easy Methods)**

**3. Round to Nearest 5 Which is Closer Using Excel VBA**

First of all, we’ll develop a **Macro** to round all the marks to the nearest 5’s which are closer. The **VBA** code will be:

⧭** VBA Code:**

```
Sub Round_to_Closer_Nearest_5()
SheetName = "Sheet1"
DataSet = "C4:C13"
Output = "D4:D13"
Set Input_Range = Worksheets(SheetName).Range(DataSet)
Set Output_Range = Worksheets(SheetName).Range(Output)
For i = 1 To Input_Range.Rows.Count
For j = 1 To Input_Range.Columns.Count
Number = Input_Range.Cells(i, j)
If Int(Number / 5) = (Number / 5) Then
Nearest_5 = Number
Else
k = 0
While k < Number
k = k + 5
Wend
If (k - Number) > (5 / 2) Then
Nearest_5 = k - 5
Else
Nearest_5 = k
End If
End If
Output_Range.Cells(i, j) = Nearest_5
Next j
Next i
End Sub
```

⧭** Output:**

Run the code. It’ll convert the marks in range **C4:C13** of **Sheet1** to the nearest 5’s which are closer in range **D4:D13** of the worksheet.

⧭** Notes:**

Again, the first 3 lines of the code contain the inputs to the code (**SheetName, DataSet,** and **Output**). Don’t forget to change them according to your needs.

**Read More:** **Rounding to Nearest Dollar in Excel (6 Easy Ways)**

**Designing a User-Defined Function to Round a Range of Values to the Nearest 5 Using Excel VBA**

Finally, we’ll design a user-defined function to round the marks to the nearest 5’s.

The **VBA** code will be:

⧭** VBA Code:**

```
Function RoundtoNearest5(Input_Range As Range, Identifier As String)
Dim Output As Variant
ReDim Output(Input_Range.Rows.Count - 1, Input_Range.Columns.Count - 1)
For i = 1 To Input_Range.Rows.Count
For j = 1 To Input_Range.Columns.Count
Number = Input_Range.Cells(i, j)
If Int(Number / 5) = (Number / 5) Then
Nearest_5 = Number
Else
k = 0
While k < Number
k = k + 5
Wend
If Identifier = "u" Then
Nearest_5 = k
ElseIf Identifier = "l" Then
Nearest_5 = k - 5
ElseIf Identifier = "c" Then
If (k - Number) > (5 / 2) Then
Nearest_5 = k - 5
Else
Nearest_5 = k
End If
Else
Nearest_5 = Number
End If
End If
Output(i - 1, j - 1) = Nearest_5
Next j
Next i
RoundtoNearest5 = Output
End Function
```

⧭** Output:**

This function takes 2 arguments. One is the range of numbers, and the other is a string defining whether we want the upper nearest 5, the lower nearest, or the closer nearest one.

**“u”** for the upper nearest value.

**“l”** for the lower nearest value.

And **“c”** for the closer nearest value.

Select any range of cells equal to range **C4:C13** and insert this formula:

**=RoundtoNearest5(C4:C13,”u”)**

It’ll convert the range C4:C13 to the upper nearest 5’s.

Similarly, the formula **RoundtoNearest5(C4:C13,”l”)** will turn it to the lower nearest values.

And the formula **RoundtoNearest5(C4:C13,”c”)** will turn it to the nearest values which are closer.

**Read More: ****Round Time to Nearest 5 Minutes in Excel (4 Quick Methods)**

**Things to Remember**

While you are working with a range of cells, 3 loops work within the code simultaneously. Therefore, a single mistake in the code can cause your code to run for infinity and can cause damage to the processor of your computer. So be careful and check every line of the code carefully before running the code.

**Conclusion**

Therefore, this is the process to round any number or a range of numbers to the nearest 5 using **VBA** in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site **ExcelDemy** for more posts and updates.