Autosort select range based on change in formula value in multiple cells

[ejkaus]

Hi All,

I have a small range of results that I would like to automatically sort when the formula changes. Below is what the data looks like;

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Drug A[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Drug B[/TD]
[TD]80[/TD]
[TD]B3-B2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Drug C[/TD]
[TD]110[/TD]
[TD]B4-B3[/TD]
[/TR]
</tbody>[/TABLE]

I would like to automatically resort A2:B4 from smallest to largest, so that the result in columns C is always positive. Note that column B is based on formula.

Below is the code I have and it works fine if I run the macro, but I can't figure out how to change it so that it automatically runs when the numbers in column B change.

Private Sub Worksheet_Change()
Static oldval1
Static oldval2
Static oldval3
If Range("B2").Value <> oldval1 Or Range("B3").Value <> oldval2 Or Range("B4").Value <> oldval3 Then
oldval1 = Range("B2").Value
oldval2 = Range("B3").Value
oldval2 = Range("B4").Value



    ActiveWorkbook.Worksheets("Results").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Results").Sort.SortFields.Add Key:=Range("B2:B4" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Results").Sort
        .SetRange Range("A2:B4")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
End Sub

This problem is similar to this one: http://www.mrexcel.com/forum/excel-...ions-run-macro-cell-value-change-formula.html however this code didn't work for me, I couldn't get it to run automatically.

Any ideas for what I need to change to get this code to run automatically?

Cheers, Eliza

 

[ejkaus]

I found the answer for myself and thought I would answer for anyone else who is interested!

I changed the sub to worksheet_activate, instead of worksheet_change. I was told that this reduces the computing burden of the code. I also added in "Application.ScreenUpdating = False" at the beginning and "Application.ScreenUpdating = True" at the end for the same reason. I understand that this makes VBA run through the entire code before doing anything. Not sure which one "fixed" the problem, but either way it works now!

Cheers, Eliza.

 Private Sub Worksheet_Activate() 
 Application.ScreenUpdating = False
Static oldval1
Static oldval2
Static oldval3
If Range("B2").Value <> oldval1 Or Range("B3").Value <> oldval2 Or Range("B4").Value <> oldval3 Then
oldval1 = Range("B2").Value
oldval2 = Range("B3").Value
oldval2 = Range("B4").Value
' Resort Macro
    ActiveWorkbook.Worksheets("Results").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Results").Sort.SortFields.Add Key:=Range("B2:B4" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Results").Sort
        .SetRange Range("A2:B4")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    End If
    
    Application.ScreenUpdating = True
End Sub