Inserting a common formula into VBA

[BrianExcel]

In A1 I am inserting a value in minutes, say 120 as an example.

I am trying to write a formula that will take the target I just entered, divide by 60 and input the value into cell B2.

Can anyone help? Right now I am trying:

Target.Offset(0, 1) = Quotient(Target \ 60)

but it's not working....

 

[Alphacsulb]

Why not throw a formula in there?

Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/60"
Range("B2").Select

 

[BrianExcel]

Mostly because it won't always be the same cell or even column...I simplified it for the post I did, but my end goal is this...

The user will enter time in minutes into cells either in columns A:C, E:G, etc.,). I need vba code that runs each time a value is entered in those cells that wraps the time and converts it to 0:00 regardless of the value entered. If the user enters 5 minutes, it should change to 0:05. If the user enters 60 minutes, it should change to 1:00. If they enter 105 minutes, it should change to 1:45, etc.

I am trying to figure out how to do that but I figured getting the "60" divisor in there was the first step.

Any suggestions?

 

[Alphacsulb]

What code do you have previous to this line?

 

[BrianExcel]

Nothing yet at this point. I have a piece of code someone sent to me before that works pretty well, but there are some flaws with it.

1. When you get above 100 minutes the time doesn't work right. When you enter 100 minutes in the current code, instead of showing 1:40 it shows 1:00, even though if you enter 60 it also shows 1:00 (the 60 is correct, but the 100 is not).

2. When the user creates a new row and tries to enter times into the specified columns it does one of two things wrong, either (1) multiplies the value by 24 or (2) creates a value with a decimal point. So if the user enters 10 (into the new row) instead of showing 0:10 it shows either (1) 240:00 or (2) 10:00.

Riddle me that?!

The code I am using right now is below...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vVal

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("O:Q, S:U, W:Y, AA:AC, AE:AG")) Is Nothing Then Exit Sub
    
    With Target
        vVal = Format(.Value, "0000")
        If IsNumeric(vVal) And Len(vVal) = 4 Then
            Application.EnableEvents = False
            .Value = Left(vVal, 2) & ":" & Right(vVal, 2)
            .NumberFormat = "[h]:mm"
        End If
    End With
    Application.EnableEvents = True
End Sub

 

[Alphacsulb]

Here is an idea:

Column A, B, C - Format - Custom - Type = h:mm

Put this code under Sheet1

Dividing by 1440 will give you the result you want. You can change the range to what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
    Const k = 1440
    Dim cell As Range
     
    If Not Intersect(Target, Range("A1:C100")) Is Nothing Then
        For Each cell In Target
            If cell.Value <> "" Then
                Application.Calculation = xlCalculationManual
                Application.EnableEvents = False
                cell.Value = cell.Value / k
                Application.Calculation = xlCalculationAutomatic
                Application.EnableEvents = True
            End If
        Next cell
    End If
     
End Sub