In this article, we will demonstrate how to divide without remainder in Excel in two ways using VBA. To illustrate the methods, we’ll use the following dataset of 5 dividends in cells B5:B9 and 5 divisors in cells C5:C9. We’ll perform the division process and get 5 quotients in cells D5:D9 as integers without remainders.
Method 1 – Using the Backslash (\) Operator
Steps:
- Go to the Developer tab >> Visual Basic tool.
The VB Editor window will appear.
- Go to the Insert tab >> Module option.
A new module named Module1 will be created.
- Double-click on Module1 and insert the following VBA code in the code window:
Sub Backward_Slash_Division()
Range("D5") = Range("B5") \ Range("C5")
Range("D6") = Range("B6") \ Range("C6")
Range("D7") = Range("B7") \ Range("C7")
Range("D8") = Range("B8") \ Range("C8")
Range("D9") = Range("B9") \ Range("C9")
End Sub- Press Ctrl + S to save the code.
A dialog box will appear.
- Click the No button.
The Save As dialog box will appear.
- Choose the Save as type: option as .xlsm file.
- Click on the Save button.
- Close the code window.
- Go to the Developer tab >> Macros tool.
The Macro window will appear.
- Click on the Backward_Slash_Division macro.
- Click on the Run button.
The VBA code will run and return the quotient without remainders for the divisions.
- If you need your result somewhere else, declare that cell range inside the left side’s Range function. And, if the dividend and divisor are in different cells apart from the used range in the example, declare those cell ranges in the right side’s Range function as required.
- If you use forward slash (/) instead of backslash (\), you will not get the integer quotient, but the actual quotient with decimals.
Method 2 – Using VBA Quotient Function
Steps:
- Following the first method, insert a module named Module2 in your Excel workbook.
- Insert the following VBA code in the Module2 code window and press Ctrl + S:
Sub Division_Quotient_Function()
Range("D5") = WorksheetFunction.Quotient(Arg1:=Range("B5"), Arg2:=Range("C5"))
Range("D6") = WorksheetFunction.Quotient(Arg1:=Range("B6"), Arg2:=Range("C6"))
Range("D7") = WorksheetFunction.Quotient(Arg1:=Range("B7"), Arg2:=Range("C7"))
Range("D8") = WorksheetFunction.Quotient(Arg1:=Range("B8"), Arg2:=Range("C8"))
Range("D9") = WorksheetFunction.Quotient(Arg1:=Range("B9"), Arg2:=Range("C9"))
End Sub
- Save the Excel file in .xlsm format following the previous method.
- Go to the Developer tab and click on the Macros tool to open the Macro window.
- Choose the Division_Quotient_Function macro and click on the Run button.
The desired result of integers without any remainder will be returned.
If you need your result somewhere else, declare that cell range inside the left side’s Range function. And, if the dividend and divisor are in different cells apart from the used range in the example, declare those cell ranges in the Arg: argument as required.
Download Practice Workbook
Get FREE Advanced Excel Exercises with Solutions!
