Macro generated by maximum of absolute value of cells

[Nelson31]

Numeric data is streamed into cells B8, B10, B12, B14, B16 and B18.

Cell B23 is the maximum of the absolute value of the above cells at any time, so the formula in B23 is :
=MAX(ABS($B$8),ABS($B$10),ABS($B$12),ABS($B$14),ABS($B$16),ABS($B$18))

Cell B5 is a user-defined constant, in our case 13.00, and is the threshold value that will trigger one of the macros.

So, in the case below, B23 = 8.00, and because 8.00 < 13.00 no macro is called.

If, however, B5 was 7.50, then since B23 (8.00) >= 7.50, and B14 is a positive value, Macro_7 is to be called. Had B14 been -8.00, then Macro_8 is to be called.

This process is to be started when the user presses the START button, which has macro START assigned to it. Once a macro is called, the process ends till the user restarts it.

I am having trouble coding this in VBA and would appreciate any assistance.

 

[fadee2]

hi nelson,
Do you mean Value in B23 is the reference value for each value present in column B starting from B8 to B18. I mean If B23 = 2.00 then macro_1 will be executed and if b23 = -3.50 then Macro_6 will be executed and so...., but only if B23 is always greater then your threshold value?

 

[Nelson31]

hi nelson,
Do you mean Value in B23 is the reference value for each value present in column B starting from B8 to B18. I mean If B23 = 2.00 then macro_1 will be executed and if b23 = -3.50 then Macro_6 will be executed and so...., but only if B23 is always greater then your threshold value?

Hi fadee2,

B23 is the maximum of the absolute values of B8 to B18. If B23 >= the threshold value, then the corresponding macro in B8 to B18 will be run, depending on whether the actual value is either positive or negative. So in the example above, no macro is called. If, however, B5 (threshold) had been 5.00, then Macro_7 is called.

 

[fadee2]

You can do the following,

Private Sub ABC()

For i = 8 To 18 Step 2
If Cells(23, 2).Value > 0 Then name_sub = "macro_" & i
If Cells(23, 2).Value < 0 Then name_sub = "macro_" & i + 1
    If Cells(23, 2).Value > Cells(5, 2).Value Then
        Cells(i, 2).Select
        If Cells(23, 2).Value = Cells(i, 2) Then
            Application.Run name_sub
            Exit Sub
        Else
        End If
    End If
Next i
MsgBox "Match Criteria invalid. No Macro was executed.", vbExclamation

End Sub

Private Sub Macro_8()
 MsgBox "Macro_8 Executed"
End Sub

Private Sub Macro_9()
 MsgBox "Macro_9 Executed"
End Sub

Private Sub Macro_10()
MsgBox "Macro_10 Executed"
End Sub

Private Sub Macro_11()
MsgBox "Macro_11 Executed"
End Sub

Private Sub Macro_12()
MsgBox "Macro_12 Executed"
End Sub

Private Sub Macro_13()
MsgBox "Macro_13 Executed"
End Sub

Private Sub Macro_14()
MsgBox "Macro_14 Executed"
End Sub

Private Sub Macro_15()
MsgBox "Macro_15 Executed"
End Sub

Private Sub Macro_16()
MsgBox "Macro_16 Executed"
End Sub
Private Sub Macro_17()
MsgBox "Macro_17 Executed"
End Sub
Private Sub Macro_18()
MsgBox "Macro_18 Executed"
End Sub
Private Sub Macro_19()
MsgBox "Macro_19 Executed"
End Sub

All procedures are created with respect to row numbers, we can call those macros from with our main procedure by using a variable "i", since we are checking each row value.

But I would like to ask, what does these macros do? and why are all of these different? Why don't you create a single macro with multiple conditions and execute condition, that matches with your criteria??

 

[Nelson31]

fadee2, thanks for your reply. Yes, you are correct, it would be more efficient if it were done in one macro rather than 12.

 

[fadee2]

You're welcome...