# Macro generated by maximum of absolute value of cells

#### Nelson31

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

### Excel Facts

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

##### Active Member
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
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?

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.

##### Active Member
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??

#### Nelson31

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

##### Active Member
You're welcome...

Replies
3
Views
95
Replies
1
Views
282
Replies
1
Views
88
Replies
4
Views
206
Replies
25
Views
981

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.

### Which adblocker are you using?

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

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