Excel Macros Training Videos

Using offset, activecell and inputbox features
in Excel to automate calculations and make
data entry easy

Automatic calculations and easy data entry using a macro:
The offset property in combination with the activecell property helps you to control and access the cell where you want to place your data. The inputbox function makes data entry easier and intuitive. Using variable definitions in co-operation with the inputbox function you can now easily automate the calculations. The charm of this Excel macro is that you can modify it to do much more. The macro code has been placed here for your ready reference.
Note: This example helps to understand how the different range and offset properties are used to move around the Excel worksheet. It is not a complete solution

Sub test()
Dim price, qty, amount As Single
Range("A1") = "Item"
Range("B1") = "UnitPrice"
Range("C1") = "Quantity"
Range("D1") = "Amount"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell = InputBox("Enter the name of item")
ActiveCell.Offset(0, 1).Range("A1").Select
price = InputBox("Enter the price")
ActiveCell = price
ActiveCell.Offset(0, 1).Range("A1").Select
qty = InputBox("Enter the qty")
ActiveCell = qty
ActiveCell.Offset(0, 1).Range("A1").Select
amount = price * qty
ActiveCell = amount
ActiveCell.Offset(1, -3).Range("A1").Select
ActiveCell.Offset(-1, 0).Range("A1").Select
End Sub
 
 
Using offset, activecell and inputbox features in Excel to automate calculations and make data entry easy