Array Formula that I can automate RRS feed

  • Question

  • Array Formula that I can automate You are subscribed to this thread


    I have an array formula (which I think is the way to go) that is extracting multiple criteria.  I can preform the steps manually, but I want to be able to put the steps in a macro (easier for my user group).

    I have watched many "you tube" videos on using vba with an array formula but I am not sure how to adapt the formula into the code.

    I need the formula range to be dynamic in the array formula.  My example is showing fixed ranges.  


    Hope my example helps in what I am trying to accomplish.  Hope I put in the correct forum.


    Saturday, January 27, 2018 7:16 PM

All replies

  • Hi J,

    I cannot hardly understand what you want to do. 
    Where is Array? Values in column C don't look like Date. Are they Date? Are values in column X string of "TRUE"/"FALSE", or are they results of some formula?
    When you insert a screenshot, you make Row number visible (since your formulas include row number). 

    I'm afraid you need to explain more details more clearly.



    Sunday, January 28, 2018 7:34 AM

    You had asked,"I need the formula range to be dynamic in the array formula.  My example is showing fixed ranges."

    below is an example to get the range dynamically in array formula.

    Sub demo()
    Dim rng As Range
    Set rng = Selection
    Worksheets("Sheet1").Range("A1:A10").FormulaArray = "=AVERAGE(IF(" & rng.Address & ">0," & rng.Address & ",FALSE))"
    End Sub

    Above code will add selected range on the sheet in your formula.

    You can modify the code as per your requirement.


    Range.FormulaArray Property (Excel)



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Monday, January 29, 2018 6:46 AM