I want to ask the user for a number, add that number to the value in the active cell, then place the result in a cell that is three rows up and two columns right of the active cell.
Sub AddNumbersB()
Dim x As Double
x = InputBox("Please enter a number")
ActiveCell.Offset(-3, 2).Value = x
You need to add that number x to the value of ActiveCell. Right now you just write the value x so change:
ActiveCell.Offset(-3, 2).Value = ActiveCell.Value + x
If you would make this a solid procedure you would do something like the following:
Check if you can move 3 up and 2 right from the ActiveCell. If that's not possible we need to choose another cell first.
Make sure you use the Application.InputBox method where you can specify a Type:=1 so this box only accepts numbers. Note Application.InputBox is different from InputBox (which does not allow types).
Also make sure to define the return value as Variant because the Application.InputBox method will either return a number or return False as Boolean in case of cancel.
Then check if the user pressed the cancel button (so it doesn't perform the calculation and exits instead).
The Application.InputBox method will return a False of type Boolean on cancel. Why can't we just check for RetVal = False to identify the cancel? Because if the user enters 0 as a number this will be treated as cancel aswell because False and 0 is considered to be equal in VBA. Therfore we need to check if the type is Boolean too!
Finally if all conditions are met and the user entered a number and pressed OK we can perform the addition and write the result.
Option Explicit
Public Sub AddNumbersB()
' check if active cell can move 3 up and 2 right
If ActiveCell.Row > 3 And ActiveCell.Column < ActiveSheet.Columns.Count - 2 Then
Dim RetVal As Variant
RetVal = Application.InputBox("Please enter a number", Type:=1) 'Type 1 only allows to input numbers
' check if user pressed cancel and exit
If VarType(RetVal) = vbBoolean And RetVal = False Then Exit Sub
ActiveCell.Offset(-3, 2).Value = ActiveCell.Value + RetVal
Else
MsgBox "Can't move 3 up or 2 right. Choose another cell where this is possible."
End If
End Sub
x = x + ActiveCell.Value.