Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Sub Fill_formula()
lastrow = ActiveSheet.Columns(1).Find(What:="#DIV/0!", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row - 1
For i = 2 To lastrow - 1
ActiveSheet.Columns(i).EntireColumn.Delete
ActiveSheet.Cells(1, i) = i
ActiveSheet.Cells(1, i).NumberFormat = "###0"
Next i
'Set Rng = ActiveSheet.Range("A1:A" & lastrow)
For r = 2 To lastrow
For i = 2 To lastrow - r + 1
If i = lastrow - r + 1 Then
ActiveSheet.Cells(r, i).Formula = "=SUM(A" & r & ":A" & lastrow & ")/" & i
ActiveSheet.Cells(r, i).NumberFormat = "#,##0.00"
Exit For
Else
ActiveSheet.Cells(r, i).Formula = "=(R[0]C[" & i * -1 + 1 & "]*(2/(" & i & "+1))+R[1]C[" & i - i & "]*(1-(2/(" & i & "+1))))"
ActiveSheet.Cells(r, i).NumberFormat = "#,##0.00"
End If
Next i
Next r
End Sub
Sub Fill_formula()
lastrow = ActiveSheet.Columns(1).Find(What:="#DIV/0!", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row - 1
max_column = 4 '<--- Change the column here
For i = 2 To max_column
ActiveSheet.Columns(i).EntireColumn.Delete
ActiveSheet.Cells(1, i) = i
ActiveSheet.Cells(1, i).NumberFormat = "###0"
Next i
For r = 2 To lastrow
For i = 2 To lastrow - r + 1
If i > max_column Then Exit For
If i = lastrow - r + 1 Then
ActiveSheet.Cells(r, i).Formula = "=SUM(A" & r & ":A" & lastrow & ")/" & i
ActiveSheet.Cells(r, i).NumberFormat = "#,##0.00"
Exit For
Else
ActiveSheet.Cells(r, i).Formula = "=(R[0]C[" & i * -1 + 1 & "]*(2/(" & i & "+1))+R[1]C[" & i - i & "]*(1-(2/(" & i & "+1))))"
ActiveSheet.Cells(r, i).NumberFormat = "#,##0.00"
End If
Next i
Next r
End Sub
Sub Fill_formula()
lastrow = ActiveSheet.Columns(1).Find(What:="#DIV/0!", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row - 1
For i = 2 To lastrow
If ActiveSheet.Cells(1, i).Value = "" Then Exit For
max_column = i
Next
For r = 2 To lastrow
For i = 2 To max_column
mcolumn = ActiveSheet.Cells(1, i).Value
If mcolumn = "" Or mcolumn > lastrow - r + 1 Then
ElseIf mcolumn = lastrow - r + 1 Then
ActiveSheet.Cells(r, i).Formula = "=SUM(A" & r & ":A" & lastrow & ")/" & mcolumn
ActiveSheet.Cells(r, i).NumberFormat = "#,##0.00"
'Exit For
Else
ActiveSheet.Cells(r, i).Formula = "=(R[0]C[" & i * -1 + 1 & "]*(2/(" & mcolumn & "+1))+R[1]C[" & i - i & "]*(1-(2/(" & mcolumn & "+1))))"
ActiveSheet.Cells(r, i).NumberFormat = "#,##0.00"
End If
Next i
Next r
End Sub
=IF(ROW()+B$1<13,(A2*(2/(B
paste this formula in B2 and copy it down and across
See attached file
Saqib
Copy-of-Xl0000018.xls