VBA - sort in ascending and descending order (based on if value is positive or negative)

[macroos]

Hi All.

I would like to sort my set of data (contains both negative and positive numbers) based in ascending order (if the values are negative) and in descending order (if the values are positive).
How can I do that?
Below, I was able to sort the data on column E in ascending order.
I need help in sorting in descending order if the values are positive.

Sub Step5()


Dim strDataRange As Range
Dim keyRange As Range
Dim lrow As Long
lrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row

Set strDataRange = Range("B2:L" & lrow)
Set keyRange = Range("E1")
strDataRange.Sort Key1:=keyRange, Order1:=xlAscending

End Sub

 

[Rick Rothstein]

Yes, there are positive and negative numbers in Column E.

When I clicked the macro, it sorted all in ascending order and then the error pops up.
Only the negative numbers were sorted the way I wanted.

Hmm, just so you know, the code I posted works on the test worksheet that I set up. Any chance you can post your workbook to DropBox so that I (or anyone else who might want to try their hand at answering your question) can download it in order to try my (their) code directly on your actual data? If you do not want do that, but if you trust that I will not abuse your data, you can then send it directly to me at...

rick DOT news AT verizon DOT net

If you do that, please put the thread title in the email so that I can more easily find this thread again.

 

[macroos]

I emailed to you.

 

[Rick Rothstein]

I emailed to you.

I got it. The problem was that your negative numbers are display wrapped in parentheses, not prefixed with a minus sign. Change this line of code...

FirstPositive = Columns("E").Find("[B][COLOR="#FF0000"][SIZE=4]-[/SIZE][/COLOR][/B]*", , xlValues, , xlRows, xlPrevious, , , False).Row + 1

to this and the macro should work fine...

FirstPositive = Columns("E").Find("[B][COLOR="#FF0000"][SIZE=4]([/SIZE][/COLOR][/B]*", , xlValues, , xlRows, xlPrevious, , , False).Row + 1

 

[macroos]

Awesome! Thank you so much!