macro to run a procedure based on the result

[mtawheed]

I have a cell which can contain a result based on calculations done in the sheet. I need a macro to run a procedure based on the result. if the result is higher than 0.5, i need this procedure to takw place. Thank you for your help!

Mohamed Tawheed

 

[al_b_cnu]

Hi Mohamed,

HOw about a worksheet Calculate event. The following example looks at each value in column B and places the text "Greater than 0.5" in the corresponding row in column C if the value exceeds 0.5:

Private Sub Worksheet_Calculate()
Dim R As Range

For Each R In Range("B1:B" & Range("B65536").End(xlUp).Row)
    If R.Value > 0.5 Then R.Offset(0, 1).Value = "Greater than 0.5"
Next R
End Sub

 

[mtawheed]

Dear Alan ,
Thank you for the reply, but i think this is not working for me, I need to perform the following action when cell L73 is higher than 0.5

Private Sub Worksheet_Calculate()

If Range("L73") > 0.5 Then
Range("AF3:AF72").Select
Selection.Copy
Range("AI3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("AI3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If

End Sub

Thank you

 

[al_b_cnu]

Hi Mohamed,

Try this code:

Private Sub Worksheet_Calculate()

If Range("L73") > 0.5 Then
    Application.EnableEvents = False
    With Range("AI3:AI72")
        .Value = Range("AF3:AF72").Value
        .Sort Key1:=Range("AI3"), Order1:=xlAscending, Header:=xlGuess, _
                    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                    DataOption1:=xlSortNormal
    End With
    Application.EnableEvents = True
End If

End Sub

 

[mtawheed]

ADVERTISEMENT

Alan, greatly appreciate your, but nothing works , when i change any cell to change the result value in cell L73, nothing appears, like I've done nothing at all..

thank you

 

[al_b_cnu]

Hi Mohamed,

Where have you put the code - did you right-click the sheet tab, select 'View Code' and paste the code into the code window?

 

[mtawheed]

Hi Alan

I simply put the code in a module in the visual basic editor, isn't that right?

 

[mtawheed]

oh, sorry Alan , i realised what I've done wrong, many many thanks for your help, I've done exactly what you said and it works, thank you very much