Macro to insert formula into cell with dynamic range

[mikeh44]

Hi All,

I'm struggling to write a macro with a dynamic range. I'm trying to insert a covariance formula into a cell with a range which needs to be changed depending on the users date selection. Below is an example:

Start Date: 1/1/2010
End Date 6/5/2023

Insert into cell A1 =covar.s(1st comparison with data with dates selected above, 2nd comparison data with dates selected above)

 

[Jeffrey Mahoney]

What version of Excel do you use? If you have office 365, you can use FILTER to reduce your data range within the formula. Or you can use Filter in a different range to provide a new smaller data set that you formula uses.

A Macro could pull out the data from the main dataset and put it in a temporary place for your formula. Each time the dates are changed, the macro builds a new temporary dataset.