Macro generated by maximum of absolute value of cells

Nelson31

New Member
Joined
Oct 30, 2018
Messages
5
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.

Macro.PNG
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

fadee2

Active Member
Joined
Nov 7, 2020
Messages
337
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Oct 30, 2018
Messages
5
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

Active Member
Joined
Nov 7, 2020
Messages
337
Office Version
  1. 2019
Platform
  1. Windows
You can do the following,

VBA Code:
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??
 
Solution

Nelson31

New Member
Joined
Oct 30, 2018
Messages
5
fadee2, thanks for your reply. Yes, you are correct, it would be more efficient if it were done in one macro rather than 12.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,244
Members
416,963
Latest member
samfuge

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