Use Fill Handle On Function

    Hi Everyone,


    I have a function =TimeSinceIns(3) in cell K3. Where the 3 represents the row number.


    I would like to fill the function all the way down to cell k100. And be able to do this reguarly.


    Unfortunately when I use the fill handle it leaves 3 as 3 when I want it to be changing to the appropriate row no.


    Ie in cell k4 would be =TimeSinceIns(4) and so on.


    Any ideas?

    Re: Use Fill Handle On Function


    Hi Reaf,


    Here's a VBA solution. Paste the code in a standard module and assign a hot key for convenience.


    This one will fill the _Selected_ cells:
    [vba]
    Option Explicit


    Sub FillFormulaSelected()


    Dim cel As Range


    Application.EnableEvents = False
    Application.ScreenUpdating = False


    For Each cel In Selection
    cel.Value = "=TimeSinceIns(" & cel.Row & ")"
    Next cel

    Application.EnableEvents = True
    Application.ScreenUpdating = True


    End Sub
    [/vba]


    This one will fill the defined range (K3:K100)


    [vba]


    Sub FillFormulaRange()


    Dim cel As Range
    Dim rng As Range


    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Set rng = Range("K3:K100") 'change range here

    For Each cel In rng
    cel.Value = "=TimeSinceIns(" & cel.Row & ")"
    Next cel


    Application.ScreenUpdating = True
    Application.EnableEvents = True


    End Sub
    [/vba]


    Cheers,


    dr

    Re: Use Fill Handle On Function


    Hi Dr,


    Just gave that a whirl and it works. Thanks heaps. I was kinda of hoping to avoid using a macro though. I'll keep looking but atleast now I have a solution.


    Thanks again.


    Sam

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login