1. #1
    Roscoe_Word
    Roscoe_Word's Avatar Become A Pro!
    Join Date: 02-28-12
    Posts: 2,587
    Betpoints: 1175

    VBA get AVE MARGIN of VICTORY (or DEFEAT)

    Hello SBR Crew. Hope everyone is holding up well during the pause.

    I have a teams' margs of victory in cells in column C

    I just want VBA to show the AVERAGE of the last 5 cells in that column, column C

    Does anyone know this line of code?
    -------------------------------------------------------
    L5AveMarg = Average of last 5 cells in column C
    ---------------------------------------------------------
    I know I have this display right

    MsgBox "Last 5 Ave = " & L5AveMarg
    -------------------------------------------------------------

    I know there are (Worksheet Functions) and (application functions) but I just can't get it right.

    Thanks fellas.

    I'm hoping only another couple of weeks before the sports betting world returns to normal.

  2. #2
    Waterstpub87
    Life-Style Arbitrage
    Waterstpub87's Avatar SBR PRO
    Join Date: 09-09-09
    Posts: 3,513
    Betpoints: 24

    Code in next post
    Last edited by Waterstpub87; 03-29-20 at 02:19 PM.

  3. #3
    Waterstpub87
    Life-Style Arbitrage
    Waterstpub87's Avatar SBR PRO
    Join Date: 09-09-09
    Posts: 3,513
    Betpoints: 24

    Oh ok, you always need it to dynamically select the last five cells of column C:

    Add this to your code:

    Sub Getlast5average()
    Bottom = Range("C1").End(xlDown)
    myrng = Range(Cells(Bottom - 4, "C"), Cells(Bottom, "C"))

    ls5m = Application.WorksheetFunction.Average(my rng)

    MsgBox (ls5m)
    End Sub

  4. #4
    Roscoe_Word
    Roscoe_Word's Avatar Become A Pro!
    Join Date: 02-28-12
    Posts: 2,587
    Betpoints: 1175

    Thanks WP. I think I'm gettin close, however I get this when running the program:

    Run-time error '1004':
    Unable to get the Average property of the WorksheetFunction class

    The last 5 ave in column C should be 12.4


    date line marg
    April 2, 2020 7.5 11.0
    April 3, 2020 7.5 11.0
    April 4, 2020 8.0 4.0
    April 5, 2020 8.0 10.0
    April 6, 2020 7.5 9.0
    April 7, 2020 7.5 6.0
    April 8, 2020 9.0 5.0
    April 9, 2020 9.0 7.0
    April 10, 2020 8.0 3.0
    April 11, 2020 7.5 7.0
    April 12, 2020 7.5 9.0
    April 13, 2020 15.0 36.0

    I took out the space in (my rng) in line 4 of the code, making it (myrng) and
    I took out the parenthesis in line 5 of the code: MsgBox (ls5m) to MsgBox ls5m

    Thanks again for the help, WP

  5. #5
    Waterstpub87
    Life-Style Arbitrage
    Waterstpub87's Avatar SBR PRO
    Join Date: 09-09-09
    Posts: 3,513
    Betpoints: 24

    If you just use a formula, can you get an average? It might be because your items in C might be strings, and not floats.

    Correct on the parenthesis in line five. I am too used to python now, where the command is print()

    I pasted the code into another workbook and ran it, with 1 2 3 4 5 in column C and it worked fine.

  6. #6
    Roscoe_Word
    Roscoe_Word's Avatar Become A Pro!
    Join Date: 02-28-12
    Posts: 2,587
    Betpoints: 1175

    Excellet....Thank you.

    I tweaked this line and its workin good now.

    Bottom = Range("C1").End(xlDown)

    TO:

    Bottom = Range("C1").End(xlDown).Row

    Thanks again my friend. Lets get this Corona thing over and get life back to normal.

  7. #7
    Waterstpub87
    Life-Style Arbitrage
    Waterstpub87's Avatar SBR PRO
    Join Date: 09-09-09
    Posts: 3,513
    Betpoints: 24

    Quote Originally Posted by Roscoe_Word View Post
    Excellet....Thank you.

    I tweaked this line and its workin good now.

    Bottom = Range("C1").End(xlDown)

    TO:

    Bottom = Range("C1").End(xlDown).Row

    Thanks again my friend. Lets get this Corona thing over and get life back to normal.

Top