User Definded Function in Worksheet_Change Event

[Gregorys05]

Hi All,
I am trying to get the below code to add a formula to cell after i type a value in, for example.
i type 140 in Cell B2 and the code the puts the formula =Calctime(140) in B2.

I have the code for the FormulaBelow) but i keep getting the error 1004

Also When i just Hit end on the debug the formula is in the right cell with the right value
Any Ideas??

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim Test As String
Dim T As Variant
If Target.Cells.Count > 1 Then Exit Sub
i = Target.Column
If Intersect(ActiveCell, Range("A1:J13")) Is Nothing Then
        Exit Sub
 
    Else
 
Test = Target.Value
Target.Offset(0, 0).FormulaR1C1 = "=CalcTime(" & Test & ")"
 
 
End If
 
End Sub

Function CalcTime(Seconds) As String
    Dim tData1, tData2
    If Not IsNumeric(Seconds) Then Exit Function
    If Seconds < 60 Then
        If Len(Seconds) = 1 Then
            CalcTime = "00:0" & Seconds
        Else
            CalcTime = "00:" & Seconds
        End If
    Else
        tData1 = RoundDown(Seconds / 60)
        tData2 = Seconds - tData1 * 60
 
        If Len(tData1) = 1 Then
            tData1 = "0" & tData1
        End If
 
        If Len(tData2) = 1 Then
            tData2 = "0" & tData2
        End If
 
        CalcTime = tData1 & ":" & tData2
 
    End If
End Function
Function RoundDown(Number)
    Dim tData1
    tData1 = InStr(Number, ".")
    If tData1 = 0 Then
        RoundDown = Number
    Else
        RoundDown = Left(Number, tData1 - 1)
    End If
End Function

 

[RoryA]

I'd turn events off too:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
If Target.Count > 1 Then Exit Sub
on error resume next
Application.Enableevents = False
    Set rng = Range("B:B")
    If Not Intersect(Target, rng) Is Nothing Then
        If Target.Value >= 0 Then Target.Offset(0, -1).Value = Now()
    End If
    Set rng = Range("H:H")
    If Not Intersect(Target, rng) Is Nothing Then
        If Target.Value >= 0 Then Target.Offset(0, -1).Value = Now()
    End If
   Application.EnableEvents = True
   On Error Goto 0
    ActiveWorkbook.Save
End Sub

 

[Latchmaker]

Thanks it worked great.