1

I'm trying to run a macro that runs the following formula when a cell is clicked and populates it with the rounded figure:

= Round((A1 + "0:02") * 96, 0) / 96

This takes the time from A1 and rounds it to the nearest 15 mins.

The range of cells that can be clicked for this are D5:X46.

Ƭᴇcʜιᴇ007
  • 113,841
Michael
  • 11

1 Answers1

1

One way to achieve this is with a Worksheet_SelectionChange() Sub. Place this code into the sheet module that you wish to have this functionality in.

Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("D5:X64")) Is Nothing Then Exit Sub
    Target.Formula = "= Round(($A$1 + " & Chr(34) & "0:02" & Chr(34) & ") * 96, 0) / 96"
End Sub

The IF statement checks that the click has been on the desired range. You can click on multiple cells and they will all populate

https://msdn.microsoft.com/en-us/library/office/ff194470.aspx

CallumDA
  • 1,055