Add a formula to column immediately after a range of values.

[NeilATaylor]

Hi,
An extremely simple task (I'm predicting) but very frustrating for a rookie.
How do I add a Macro that will add an =ABS() formula to the last column of a range of values, and then sort the range (including the new ABS value) by the ABS value?
Thanks for any help offered

 

[Boller]

Provided that the range consists of one area only, and assuming that you want to replace the values in the last column of the range :-

Dim rng As Range, lc#
Set rng = [B2:F50] 'or whatever
lc = Range([A1], rng).Columns.Count
Columns(lc).Insert
With Intersect(Columns(lc), rng)
    .FormulaR1C1 = "=ABS(RC[1])"
    .Offset(, 1).Value = .Value
    .EntireRow.Sort Cells(1, lc + 1), Order1:=xlAscending, Header:=xlNo
End With
Columns(lc).Delete

 

[NeilATaylor]

thats awesome boller, thanks! How would I keep the original values (in the last column) with the ABS value to the right i.e. not replacing the original value?

 

[Boller]

Dim rng As Range, lc#
Set rng = [B2:F50] 'or whatever
lc = Range([A1], rng).Columns.Count
With Intersect(Columns(lc + 1), rng.Offset(, 1))
    .FormulaR1C1 = "=ABS(RC[-1])"
    .EntireRow.Sort Cells(1, lc + 1), Order1:=xlAscending, Header:=xlNo
End With