You can use the Floor method in VBA to round the number in a cell down to the nearest multiple of significance.
Here is one common way to use this function in practice:
Sub ToFloor()
Dim i As Integer
For i = 2 To 10
Range("C" & i) = WorksheetFunction.Floor(Range("A" & i), Range("B" & i))
Next i
End Sub
This particular macro will round each value in the range A2:A10 to the nearest multiple of significance specified in the range B2:B10 and display the results in the range C2:C10.
The following example shows how to use this syntax in practice.
Example: How to Use Floor Function in VBA
Suppose we have a list of values in column A and another list of multiples of significance in column B that we’d like to round down each value to:
We can create the following macro with the Floor method in VBA to round down each value in column A to the multiple of significance in column B:
Sub ToFloor()
Dim i As Integer
For i = 2 To 10
Range("C" & i) = WorksheetFunction.Floor(Range("A" & i), Range("B" & i))
Next i
End Sub
When we run this macro, we receive the following output:
Column C shows the result of using the Floor method on each value in Column A.
For example, we can see:
- 12.2452 rounded down to the nearest multiple of .001 is 12.245.
- 14.927 rounded down to the nearest multiple of .01 is 14.92.
- -5.23 rounded down to the nearest multiple of .1 is -5.3.
And so on.
Note: You can find the complete documentation for the VBA Floor method here.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
How to Round Up Values in VBA
How to Round Down Values in VBA
How to Round Values to 2 Decimal Places in VBA
