Newbie needs help with loop RRS feed

  • Question

  • I'm trying to write a function for Excel 2013 that will add 20 non-zero numbers in a column and divide by 20 to get a moving average. I will then copy the function down 3,000 cells. I use to program VB6 but I'm lost in VBA. Can someone please tell me how I reference whatever cell the function starts in. If I knew that I believe I can then construct a loop that will test for being >0 and calculate the moving average. As an example, if the function starts in cell B1 and the data is in cell A1, I may have to look at 25 cells down from A1 to find 20 non-zero numbers. Then the function would start all over in cell B2 looking in A2 and so on.  Thanks for your help.   
    Saturday, March 15, 2014 4:44 AM


  • Hi Jack,

    If I understand correctly, you want to auto fill the function down the cells. Record macro is an easy way to get start. I write a function in B1 and I want to copy the function to the cells to B2:B13. Below is the code:

    Sub AutoFillCells()
        Range("B1").AutoFill Destination:=Range("B1:B13"), Type:=xlFillDefault
    End Sub

    Screen Shot:

    You can get more detail from article below:

    Best regards



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 17, 2014 7:00 AM