Monthly average - VBA

Posted on 2011-10-25
Last Modified: 2012-05-12
I have came up with my own solution for this but I am not if its the neatest approach to do it.

all I am trying to do is to get the monthly average of the price for the dates and populate against the cells. (in VBA)
Please see the attahed file.
It would be great if some one provide me with code that would populate the 'Monthly Avg' - Column C with the price average for that month.

Many thanks
Question by:SameerMirza
    LVL 50

    Expert Comment


    any particular reason for VBA? A formula would be much faster. Start in C2


    Confirm with Ctrl-Shift-Enter and copy down.

    cheers, teylyn
    LVL 50

    Accepted Solution

    Here's one possibility to do it in code

    Sub Macro2()
    Dim lRow As Long
    lRow = Cells(Rows.Count, "A").End(xlUp).Row
        Range("C2").FormulaArray = _
            "=AVERAGE(IF(MONTH($A$2:$A$" & lRow & ")=MONTH(A2),$B$2:$B$" & lRow & ",""""))"
        Range("C2").AutoFill Destination:=Range("C2:C" & lRow)
        With Range("C2:C" & lRow)
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        End With
        Application.CutCopyMode = False
    End Sub

    Open in new window


    Author Comment

    Thanks but in this case the records would go beyond one year
    That is multiple years.
    so we would have to add check for multiple years too.
    I tried,

    =AVERAGE(IF(AND(YEAR($A$2:$A$24)=YEAR(A2), MONTH($A$2:$A$24)=MONTH(A2)),$B$2:$B$24,""))

    But it fails. Because its trying to locate the same value in the whole range.
    LVL 50

    Expert Comment

    AND() does not work in this context. You can use this instead:


    Remember to confirm with Ctrl-Shift-Enter

    cheers, teylyn

    Author Comment

    Thanks. That seems to work fine
    Although I have written a module too (which works :)) using loops but I think your would be much quicker.

    I have another very quick one.
    I am trying to freeze pans.
    Go select row -> window -> freeze pane -
    for some reason it is freezing on 16 rows

    tried doing it from VBA too. Get the same result
    'ActiveWindow.FreezePanes = True'

    How can I do it from VBA such that it would freeze on header/first row?


    Author Closing Comment

    Please dont bother reading what i just asked. :)
    you are suppose to select the data and I was selecting the first row :/

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now