# Excel Macro to pop-up message based on if and multiplication

#### Eraclis

##### New Member
Hello Everyone!

I ran into an issue with macros and I could use some guidance! I attach a sample excel file for ease of reference.

I need to write a VBA code which needs to:
1) Check if a condition is met ("Other" or "Building") -> Column D
• If "Other", then multiply OMV (Column C) x 0.30%
• If "Building", then multiply OMV (Column C) x 0.75%
2) If the Amount (Column E) is greater than the result of the multiplication, then a warning message should pop-up indicating that we exceeded the permissible amount.

Any ideas would be greatly appreciated.

#### Attachments

• Mr Excel 10.06.2021.PNG
20.2 KB · Views: 4

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### JackDanIce

##### Well-known Member
Try:
VBA Code:
``````Sub Macro1()

Dim v As Variant
Dim party_legal As Variant
Dim x As Long

party_legal = Split("Other|0.003|Building|0.0075", "|")
x = Cells(Rows.Count, 3).End(xlUp).Row
v = Cells(2, 2).Resize(x - 1, 4).Value

For x = LBound(v, 1) To UBound(v, 1)
If v(x, 4) > v(x, 2) * CDbl(party_legal(Application.Match(v(x, 3), party_legal, 0))) Then _
MsgBox "Company " & v(x, 1) & " has exceed permissable amount!", vbExclamation, "Warning"
Next x

Erase party_legal: Erase v

End Sub``````

Tested using:
Other.xlsm
ABCDEFG
1Calc valueExpected Msgbox
21ABC100,000Other500300TRUE
32DEF200,000Building4001500FALSE
43GHI300,000Other300900FALSE
54JKL400,000Building2003000FALSE
65MNO500,000Building1003750FALSE
Main
Cell Formulas
RangeFormula
F2:F6F2=C2*IF(D2="Other",0.003,0.0075)
G2:G6G2=E2>F2

Last edited:

#### Eraclis

##### New Member
Thank you very much for the assistance and time spent! To be honest with you, there are a lot of unknown (for me) elements in the macro suggested above. Therefore, I am not sure how the wording should change if the I change the columns' order compared to your tested spreadsheet attached in your reply.

How should I change the macro's write up if columns E, F & G in your tested spreadsheet above are K, L and M in mine?

#### JackDanIce

##### Well-known Member
For K, L, M columns, try:

VBA Code:
``````Sub Eraclis()

Dim v As Variant
Dim party_legal As Variant
Dim x As Long

party_legal = Split("Other|0.003|Building|0.0075", "|")
x = Cells(Rows.Count, 2).End(xlUp).Row
'Get data from B2:Mx
v = Cells(2, 2).Resize(x - 1, 12).Value

For x = LBound(v, 1) To UBound(v, 1)
'If Kx > Cx* factor
If v(x, 10) > v(x, 2) * CDbl(party_legal(Application.Match(v(x, 3), party_legal, 0))) Then _
MsgBox "Company " & v(x, 1) & " has exceed permissable amount!", vbExclamation, "Warning"
Next x

Erase party_legal: Erase v

End Sub``````

Replies
17
Views
586
Replies
5
Views
2K
Replies
0
Views
377
Replies
2
Views
297
Replies
1
Views
562

1,141,605
Messages
5,707,355
Members
421,503
Latest member
Rickys03

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