Help please!!
Problem:
I am working on an excel project that requires the use of several array formulas. They work fine but I have found out that they slow down the performance of the file drastically when I need to update a new dataset that the array formulas reference.
Question:
I need to find a way to assign a variable the value of the array formula without having to use that formula in the sheet. Ideally, working it all out in VBA and then simply assigning the resulting value of the array formula into a specific cell. Is this possible or is there a better vba workaround?
Here is what I currently have and in need of replacing with a simple value instead (not array formula).
Range("O4").Select
ActiveCell.FormulaArray = "=MEDIAN(IF(C5=R3C13, IF(C9>0,C9, 0)))"
Also, I need to do the same for other array formulas; max, min, count, average
thanks in advance for any help!
Problem:
I am working on an excel project that requires the use of several array formulas. They work fine but I have found out that they slow down the performance of the file drastically when I need to update a new dataset that the array formulas reference.
Question:
I need to find a way to assign a variable the value of the array formula without having to use that formula in the sheet. Ideally, working it all out in VBA and then simply assigning the resulting value of the array formula into a specific cell. Is this possible or is there a better vba workaround?
Here is what I currently have and in need of replacing with a simple value instead (not array formula).
Range("O4").Select
ActiveCell.FormulaArray = "=MEDIAN(IF(C5=R3C13, IF(C9>0,C9, 0)))"
Also, I need to do the same for other array formulas; max, min, count, average
thanks in advance for any help!
Last edited:
