Excel VBA: Round to Nearest 5 (Macro and UDF)

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

VBA Code to Round to Nearest 5 in Excel VBA


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

Taking a Number to Round to Nearest 5 Using Excel VBA

⧪ 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

Checking whether a Number is Divisible by 5 or Not to Round to Nearest 5 Using Excel VBA

⧪ 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

VBA Code to Round to Nearest 5 Using Excel VBA

⧪ 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.

Data Set to Round to Nearest 5 Using Excel VBA

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

VBA Code to Round to Nearest 5 Using Excel VBA

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.

Output to Round to Nearest 5 Using Excel VBA

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


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

VBA Code to Round to Nearest 5 Using Excel VBA

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

VBA Code to Round to Nearest 5 in Excel VBA

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

Function Code to Round to Nearest 5 Using Excel VBA

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.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo