Permanent formula?
 

I have an autosort code in my spreadsheet, but one of the cells that the sort
criteria is based off of is meant to be an average of 5 other cells. When the
line is shuffled into place into the spreadsheet, the formula is lost. I want
to retain that though because the sort is meant to base off of an overall
average. Can somebody help? Here is my current formula (G2 is the cell that
averages A2:F2).

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("S2")) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
Columns("A:S").Sort _
Key1:=Range("G2"), order1:=xlDescending, _
Key2:=Range("k2"), order2:=xlDescending, _
Key3:=Range("l2"), order3:=xlDescending, _
header:=xlYes

Range("A2:S2").Insert Shift:=xlDown
Application.EnableEvents = True
End Sub

Permanent formula?
 

Check your other thread.

Orangepegs wrote:

I have an autosort code in my spreadsheet, but one of the cells that the sort
criteria is based off of is meant to be an average of 5 other cells. When the
line is shuffled into place into the spreadsheet, the formula is lost. I want
to retain that though because the sort is meant to base off of an overall
average. Can somebody help? Here is my current formula (G2 is the cell that
averages A2:F2).

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("S2")) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
Columns("A:S").Sort _
Key1:=Range("G2"), order1:=xlDescending, _
Key2:=Range("k2"), order2:=xlDescending, _
Key3:=Range("l2"), order3:=xlDescending, _
header:=xlYes

Range("A2:S2").Insert Shift:=xlDown
Application.EnableEvents = True
End Sub

--

Dave Peterson