Selected Answer
You don't need VBA for that. Follow these steps instead.
- Write the time you want added in a blank cell on your sheet.
- On the Ribbon's Home tab press Copy (or key in Ctl+C)
- Select the cells to which you wish to add the time you wrote in step 1 - one cell, one column or several columns, doesn't matter. If the range is very big, use Find & Select > Goto to specify it.
- From the Home tab on the ribbon press Paste
- In the dialog box that opens, select Paste special
- In the Paste special dialog box select Add
- Press OK to carry out the requested calculation on the selected cells.
- Clear the cell you used to hold the value to be added.
Since you insist on code, here is the code. You need to change the values of the three constants at the top of the procedure for each run of it.
Option Explicit
Sub AddMinutes()
' 29 Jan 2018
Const Minutes As Single = 30 ' change as required
Const PlusMinus As String = "plus" ' any string <> "plus" = minus
Const Target As String = "B" ' can also take "B:D" or "C2:D200"
Dim MRng As Range
Dim Plus As Integer
Dim Tgt As String
Dim Rng As Range
Dim R As Long, Rl As Long
Application.ScreenUpdating = False
Plus = IIf(InStr(1, "Plus", PlusMinus, vbTextCompare) = 1, 1, -1)
Tgt = Target
If InStr(Tgt, ":") = 0 Then Tgt = Tgt & ":" & Tgt
Set Rng = Range(Tgt)
For Each MRng In Rng.Columns
R = Cells(Rows.Count, MRng.Column).End(xlUp).Row
If R > Rl Then Rl = R
Next MRng
With Rng
R = Application.Min(.Row + .Rows.Count - 1, Rl)
Set Rng = Range(Cells(.Row, .Column), Cells(R, .Column + .Columns.Count - 1))
Set MRng = Cells(Rows.Count, .Column).End(xlUp).Offset(1)
End With
With MRng
.NumberFormat = Rng.Cells(1).NumberFormat
.Value = Minutes / 24 / 60 * Plus
.Copy
End With
' set SkipBlanks to False if you want to write the added minutes
' also in cells which were blank before
Rng.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlAdd, _
SkipBlanks:=True, _
Transpose:=False
MRng.ClearContents
With Application
.CutCopyMode = False
.ScreenUpdating = True
End With
End Sub