Solved: Changing column values to negative and in descending order

[Anomandaris]

Hi there,

I'm trying to figure out a macro that will change the value in Column D(Price) based on value in Column B. I can do it without macro but it takes more time and not as effective.

If Column B is 'B' then Price remains positive, if 'S' then it should change to negative. And they should be in descending order for each code (eg GC;J09).....so for GC;J09 all prices should be in descending order, then SC;J09 and so on...

It seems simple, but i'm stuck lol.
any ideas?

thanks a bunch

[georgiboy]

Something like this maybe...

Sub Change()
Dim rCell As Range
Dim MyRange As Range
Dim EndRow As Long

EndRow = Range("B" & Rows.Count).End(xlUp).Row

Set MyRange = Range("B2:B" & EndRow)

For Each rCell In MyRange.Cells
If rCell.Value = "S" Then
rCell.Offset(, 2).Value = "-" & rCell.Offset(, 2).Value
End If
Next

Range("A2:E" & EndRow).Sort Range("E2"), xlAscending, Range("D2"), , xlDescending

End Sub

[Anomandaris]

Thanks a lot man, it works great!