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

 

[4thephil]

(a) the only data being sorted is the data being pulled with V-LOOKUP, no other data is in the row or being pulled or relevant

(b) Removing the $ sign's creat's REF errors. Running the Macro again creats an error where the Macro has to stop.

 

[mirabeau]

4thephil,

At this point it would be a good idea if you could present some unified overall picture of what just you are trying to achieve.

Without this, I for one, have to try proceeding largely piecemeal and on guesswork as to what the overall result is supposed to be.

This, at least for me, isn't a very constructive way to go about it.

 

[4thephil]

Thank you so much for your help. It's working now! With a combination of the different formulas, heres what I did:

Off to the side (made it hidden later), I obtained the values with vlookup. Then I put ABS() around it to only return absolute values. Then sorted. From there, I matched up the sorted stores and rank, then returned their values.

Thanks again

 

[mirabeau]

OK 4thephil,

Good that it all worked for you in the end.

Actually, my aim in contributing to the thread at all was solely to indicate a way to resolve the problem stated by the thread title, whilst avoiding the use of a helper column.

Trying to solve a larger problem without a clear view of the overall picture is not usually a very fruitful exercise to attempt. Sometimes guesswork does the trick, sometimes not.

Good result anyway.