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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

al_b_cnu

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

Watch MrExcel Video

Forum statistics

Threads
1,118,796
Messages
5,574,361
Members
412,589
Latest member
ArtBOM
Top