I have a data set with a dynamic range and can write a formula to fill down. but o would like the r1c1 callouts to use a dynamic range instead of a preset. Help please!!
here is my current formula (10 columns of data)
normally I would:
Dim endRow As Long
endRow = Cells(Rows.Count, "A").End(xlUp).Row
'set formula
Range("A1").End(xlToRight).Offset(1, -2).FormulaR1C1 = _
"=IF(OR(MEDIAN(RC[-10]:RC[-1])*1.05<RC[1],MEDIAN(RC[-10]:RC[-1])*0.95>RC[1]),""Check for Potential Outliers - "" & COUNTIF(RC[-10]:RC[-1],"">""&1.05*MEDIAN(RC[-10]:RC[-1]))+COUNTIF(RC[-10]:RC[-1],""<""&0.95*MEDIAN(RC[-10]:RC[-1]))-COUNTIF(RC[-10]:RC[-1], ""=0""),"""")"
'then fill down
Range("x").AutoFill Destination:=Range("x2:x" & endRow)
but what if I have 5 or 50? how do I change RC[-10]:RC[-1] to value#1 to last value in a row and then fill formula down a column to last row.
here is my current formula (10 columns of data)
normally I would:
Dim endRow As Long
endRow = Cells(Rows.Count, "A").End(xlUp).Row
'set formula
Range("A1").End(xlToRight).Offset(1, -2).FormulaR1C1 = _
"=IF(OR(MEDIAN(RC[-10]:RC[-1])*1.05<RC[1],MEDIAN(RC[-10]:RC[-1])*0.95>RC[1]),""Check for Potential Outliers - "" & COUNTIF(RC[-10]:RC[-1],"">""&1.05*MEDIAN(RC[-10]:RC[-1]))+COUNTIF(RC[-10]:RC[-1],""<""&0.95*MEDIAN(RC[-10]:RC[-1]))-COUNTIF(RC[-10]:RC[-1], ""=0""),"""")"
'then fill down
Range("x").AutoFill Destination:=Range("x2:x" & endRow)
but what if I have 5 or 50? how do I change RC[-10]:RC[-1] to value#1 to last value in a row and then fill formula down a column to last row.
