VB Code To Average Column and Format To Time

  • I need help editing a formula that I got from another post from Dave Hawley - the code works perfectly for what I am looking for. However the formula I need is an average formula that converts the average to a time value. This is the formula that I use however, I need to keep changing it as my ranges change, so I want to add a vba code that will do this automatically
    [TABLE="width: 633"]

    [tr]


    [td]


    [TABLE="width: 633"]

    [tr]


    [td]

    =INT(AVERAGE($H$2:$H$29)) & "." & RIGHT("0" & ROUND(MOD(AVERAGE($H$2:$H$29)*60,60),0),2)


    How can I replace the above formula to the formula below "=Sum(" & strAddress & ")"

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [/tr]


    [/TABLE]



    Appreciate any help or suggestion - Monti

  • Re: VB Code To Average Column and Format To Time


    Hey monti,


    Have you thought about using a named range in the formula and then just using the macro to update the named range reference?


    If you created a named range such as "strAddress" and used that in the formula instead of $H$2:$H$29 like this:


    [TABLE="class: cms_table, width: 633"]

    [tr]


    [td]

    =INT(AVERAGE(strAddress))&"."& RIGHT("0"&ROUND(MOD(AVERAGE(strAddress)*60,60),0),2)

    [/td]


    [/tr]


    [/TABLE]


    Then your macro could just find the new size of the range and:


    Code
    Selection.Name = "strAddress"
    'Or
    Range("H2:H29").Name = "strAddress"


    Cheers,
    -Wilson

Participate now!

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