Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Adding minutes to all cells in a column

0

I need your assistance and it would be greatly appreciated. I need a VBA program to add certain amount of minutes to columns containing times.  I have a sheet containing multiple columns and they all have times in them. I want to add a constant amount of minutes to the time in each cell in the column. The old time would be overwritten with the new calculated time. Thank you for all you help.

Answer
Discuss

Discussion

Thank you for your suggestion but I need to do it programmatically. I am already aware of your method. This is the initial phase and then I need to do some other manipulation to the column times. Please provide me a VBA macro that will do it.

Thank you
zman1234 Jan 28, '18 at 4:44 pm
Add to Discussion

Answers

1

You don't need VBA for that. Follow these steps instead.

  1. Write the time you want added in a blank cell on your sheet.
  2. On the Ribbon's Home tab press Copy (or key in Ctl+C)
  3. 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.
  4. From the Home tab on the ribbon press Paste
  5. In the dialog box that opens, select Paste special
  6. In the Paste special dialog box select Add
  7. Press OK to carry out the requested calculation on the selected cells.
  8. 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
Discuss

Discussion

Thank you that worked great after I made a few mods to it. Now, I want to modify it. How do we compare times? How does excel store times internally? I want to add minutes but round it to the 10th, 15, 20,30, 40, 45, 50, 00 minute on the clock.
Once again your help is greatly appreciated. 
zman1234 Mar 1, '18 at 2:08 pm
This is a Q&A forum dedicated to teaching Excel. Please bear that in mind when you ask for help. For one, please ask questions, one at a time. Try to frame them in a way that onlookers might learn something from them. Did you know that for many Excel fans finding the question is at least as difficult as finding the answers to them?
For another, ask your questions in such a way that they, together with the answers they elicit, form a lesson in Excel. If you adhere to these conditions you  will get your project done, learn a lot of things in the process, and we shall both have a great and popular forum where we can enjoy spending some time together.
Variatus (rep: 4889) Mar 1, '18 at 11:22 pm
My apologies. I'm a rookie to this forum and forums  in general.
zman1234 Mar 2, '18 at 9:05 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login