Hi, I absolutely need your help.
I put down a function. The function seems to work as intended. What I can't do is to extend the function to entire column. Help!
I put down a function. The function seems to work as intended. What I can't do is to extend the function to entire column. Help!
Code:
[Public Function ADI() As Variant
Dim cv, N, Tot As Integer
Dim sourcerange, fillrange As Range
Dim lastrow As Integer
cv = 0
N = 0
Tot = 0
If Cells(ActiveCell.Row, 2).Value = "0" Then
cv = cv + 1
End If
For i = 3 To 18
If Cells(ActiveCell.Row, i).Value = "0" Then
cv = cv + 1
Else
Tot = Tot + cv
N = N + 1
cv = 0
End If
Next i
Tot = Tot + cv
N = N + 1
If IsNull(N) = False Then
ADI = Tot / N
Else
ADI = "needs deeper understanding"
End If
'what I tried:
'lastrow = Sheets("Sells_kg").Cells(Rows.Count, 1).End(xlUp).Row
'Set sourcerange = ActiveCell
'Set fillrange = Range(ActiveCell.Address, Cells(lastrow, ActiveCell.Column)) / Set fillrange = Range(ActiveCell, ActiveCell.End(xlDown))
'sourcerange.AutoFill Destination:=fillrange
End Function