Sort by Absolute Value, Display True Value

[4thephil]

I am trying to get a range of cells sorted through a Macro by their absolute value, but still display their true value. The values of the cell are pulled via VLOOKUP from another tab. As I couldn't post an attachment, I have included an image of an example of what I'm looking for. I believe a third column must be created to figure out their absolute values, but I'm not sure.

Here is my macro I have which currently sorts it beutifully by true value:

Range("I22:J25").Select
    Range("J22").Activate
    ActiveWorkbook.Worksheets("sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("sheet1").Sort.SortFields.Add Key:=Range("J22" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("sheet1").Sort
        .SetRange Range("I22:J25")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[wiegs187]

Third column would work. Insert the column, put in the formula =abs(cell you want) and copy it down. Then sort by the newly added column, then delete the new column and it will look like what you have.

Looks like your range is static so you can probably just record it.

 

[4thephil]

Anyway to get a macro to do all of that?

 

[jeffreybrown]

How about...

Sub Results_Sort()
    With Sheets("Sheet1")
        .Columns("C:C").Insert Shift:=xlToRight, _
            CopyOrigin:=xlFormatFromLeftOrAbove
        .Range("C1").Value = "Sort"
    With .Range("C2:C" & .Range("B" & .Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=ABS(RC[-1])"
        .Value = .Value
    End With
    .Range("A1:D" & .Cells(.Rows.Count, "A").End(xlUp).Row).Sort _
        Key1:=.Range("$C$2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        .Columns("C:C").Delete
    End With
End Sub

 

[mirabeau]

It's easy to do without an additional column.

Try

Sub sortabs()
Dim a As Range, i As Integer
Set a = Range("I22:J25")
For i = 1 To 4
    a(i, 2) = Abs(a(i, 2)) & Chr(30) & a(i, 2)
Next i
a.Sort a(1, 2), 1, Header:=xlNo
For i = 1 To 4
    a(i, 2) = Split(a(i, 2), Chr(30))(1)
Next i
End Sub

 

[4thephil]

Both of those have resulted in an error ("Compile Error, Expected End Sub"), I'm sure I'm entering it in wrong. Should I be replacing my original code completely and inserting either one of yours, or be adding to it? Where would I be adding to, what would I be removing, etc.?

Again, my original code to sort these values by True Value is:

Range("I22:J25").Select
    Range("J22").Activate
    ActiveWorkbook.Worksheets("sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("sheet1").Sort.SortFields.Add Key:=Range("J22" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("sheet1").Sort
        .SetRange Range("I22:J25")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[mirabeau]

The code I suggested should work as a standalone code and not to be included as part of another code without further discussion of just what is wanted.

You asked how to sort a range using absolute values, but displaying the true values.

To do this
1. open the worksheet on which your range is located
2. Press Alt - F11
3. In the resulting window, choose insert -> module from the menu at the top
4. Copy and paste the code from Post #4 or Post #5 above into your module window.
5. Place your cursor somewhere in the code you want to run and press F5 (or choose Run ->Sub/userform from the menu at the top).
6. Your data should then appear sorted as requested.

 

[4thephil]

Awesome, thanks! This worked great (using your code from post #5). However, if there is a value that is -10 or lower, this does't get sorted correctly. It almost seems that -10 is treated as -1, and -20 is treated as -2.

 

[mirabeau]

OK.

I can see why that might be.

Will have a look at it, but am otherwise tied up for next several hours. If nothing else happens in meantime I'll sort it out then.

 

[4thephil]

Wow, that's awesome. No rush, but I'm wow-ed @ how helpful you are Thank you for that.