VBA LARGE and ABS Formula

[gt213]

How could i get the following excel formula to work using VBA:
{=LARGE(ABS($D$18:$D$23),G23)}

In this example i have my data in cells D18:D23 and in cells G18:G23 numbers 1 to 5, so with the formula above in H18 i can drag it down to H23 to get the 1st - 5th largest absolute values.

Using VBA how could i achieve the same results?

 

[footoo]

Here's one way :

Sub FT()
Dim dRng As Range: Set dRng = [D18:D23]
Dim rRng As Range: Set rRng = [H18:H23]
Dim nRng As Range: Set nRng = [G18:G23]
rRng(1).FormulaArray = "=LARGE(ABS(" & dRng.Address & ")," & nRng(1).Address(0, 0) & ")"
rRng(1).AutoFill Destination:=rRng, Type:=xlFillDefault
rRng = rRng.Value
End Sub

 

[footoo]

And if you want to automatically update for any change to D18:D23, put this in the sheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, [D18:D23]) Is Nothing Then Call FT
Application.EnableEvents = True
End Sub

 

[footoo]

If you want to eliminate the numbers in G18:G23 :

Sub FT()
Dim dRng As Range: Set dRng = [D18:D23]
Dim rRng As Range: Set rRng = [H18:H23]
rRng.Formula = "=Abs(" & dRng(1).Address(0, 0) & ")"
rRng = rRng.Value
rRng.Sort Key1:=rRng(1), Order1:=xlDescending, Header:=xlNo
End Sub

 

[gt213]

Thanks Footoo. The solution from your first post worked perfectly for me.