Rifat Hassan
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:
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.
