# macro to run a procedure based on the result

#### mtawheed

##### New Member
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

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
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
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
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If

End Sub

Thank you

#### al_b_cnu

##### Well-known Member
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
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
Application.EnableEvents = True
End If

End Sub``````

#### mtawheed

##### New Member

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
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
Hi Alan

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

#### mtawheed

##### New Member
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 Replies
1
Views
349
Replies
4
Views
49
Replies
1
Views
92
Replies
3
Views
331
Replies
0
Views
158