Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
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
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
=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