Hi,

I have data spread over several columns across several sheets in excel file. The data starts from column C followed by its normal distribution function in the next column and so on. What I want to do is to autofill the normal distribution formula in all the cells (depending on the data size on its data column) and keeping the location of mean and standard deviation for the data column as relative (reative to last row), since now I use absolute reference scale.

Why I want that? I will have variations in the data column size and hence the ndf column needs to be modified and hence the references it uses in the formula for locating "mean" and "standard deviation". How will it be possible? The sample excel file is here: Sample.xlsx

The macro I've been using is not working, says "Reference Error" at autofill. The macro is pasted here:
Sub copyndf()
Dim i As Long, c As Long
 Dim ws As Worksheet
Dim mybook As Workbook
Workbooks.Open "C:\data2\DataAssemble1.xlsx"
Set mybook = ActiveWorkbook
    Dim lRow As Long
lRow = Cells(Rows.Count, "A").End(xlUp).Row

For Each ws In mybook.Worksheets
range("C1").Select
For c = 3 To 50 Step -2 'go through alternative columns starting from C
    
    'calculate the normal distribution function
    ActiveCell.FormulaR1C1 = "=NormDist(RC[-1], R[15]C[-1], R[16]C[-1], False)"
    
    'autofill from cell C2 until the last row as defined.
  .AutoFill Destination:=range("c2:c" & lRow)
 Next c
Next
End Sub
Thanks in advance!