macro to run a procedure based on the result

mtawheed

New Member
Joined
Mar 24, 2005
Messages
16
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
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:
Code:
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

New Member
Joined
Mar 24, 2005
Messages
16
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

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
Hi Mohamed,

Try this code:
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

New Member
Joined
Mar 24, 2005
Messages
16

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

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
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

New Member
Joined
Mar 24, 2005
Messages
16
Hi Alan

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

mtawheed

New Member
Joined
Mar 24, 2005
Messages
16
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 :p
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,832
Messages
5,855,890
Members
431,772
Latest member
dannyboi1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top