Help - Macro Not Working as Intended

[Silkk]

Hi Guys,

Im relatively new to the world of Excel VBA and have written the following code:

The macro itself is linked to 2 buttons, one for Start/Stop and a Clear Button.

When pressing Start it updates cells in M18, pressing stop updates N18. Pressing start again then updates M19, and stop again updates N19.

The problem I have, is that when it updates M/N19 it automatically overwrites the previous values in the previous cells to match, which shouldnt be happening.

Anyone have any advice?
' If statement to check counter in G1. This indicates which rountine to call If 1 then use start time, else set the end time

Sub Start()
Application.ScreenUpdating = False
Sheet5.Activate
Range("M15").Select
If Range("M15").Text = 1 Then
Call Start_Time
Else
Call End_Time
End If
Application.ScreenUpdating = True
End Sub


Sub Start_Time()
Application.ScreenUpdating = False
Sheet5.Select
Range("M16").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=NOW()" ' sets activecell to the date and time
Range("M18:N26").Select
Selection.NumberFormat = "hh:mm"
Range("M15") = 2
Range("M18").Select
Application.ScreenUpdating = True
End Sub


Sub End_Time()
Application.ScreenUpdating = False
Sheet5.Select
Range("N16").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=NOW()" ' sets activecell to the date and time
Selection.Offset(0, 1).Activate ' offset call to move cell to right hand of active cell to set the formula
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("O27").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
Range("O28").Select
Range("M18:N26").Select
Selection.NumberFormat = "hh:mm"
Range("M16").Select
Range("M15") = 1
Application.ScreenUpdating = True
End Sub


Sub Clear()
Range("M18:N26").ClearContents
Range("M18:N26").Select
Selection.NumberFormat = "hh:mm"
Range("M16").Select
Range("O18").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("O18").Select
Selection.AutoFill Destination:=Range("O18:O26"), Type:=xlFillDefault
Range("O18:O26").Select
Range("O27").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
Range("O28").Select
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

I think you need to change:

ActiveCell.FormulaR1C1 = "=NOW()"

to:

ActiveCell.Value = Now

The NOW function is volatile and updates each time the worksheet is calculated. The revised code uses VBA's Now function.

 

[Silkk]

Thanks for the quick response.

Your solution worked perfectly.

 

[Silkk]

Any reason why the macro would throw an error 1004 when trying to run after locking the sheet? I have already ensured all cells in the ranges specified are unlocked.

Run-time error '1004'
Unable to set the NumberFormat property of the Range Class


When pressing Debug, it is referring to the lines: Selection.NumberFormat = "hh:mm"

 

[Andrew Poulsom]

Did you check Allow Format cells when you protected the worksheet?

 

[Silkk]

Honestly I thought I had, but after rechecking I hadnt set that.

Thanks for the update / advice.

Has been a massive help