I was hoping to write a vba so that when a new product is added, i will get the unit price (L2/H2) and when column I, the currency row= euro, it will convert column J by 10 let's say, if not do nothing and return the cell back to the original number of column J. I have a simple formula, but i was wondering if someone can provide with a vba code
| Purchase Order and Budget Estimation.xlsm | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | VENDOR | MATERIAL NO. | DESCRIPTION | CATEGORY | CAT* | PACK SIZE | UNIT | UNIT COST | CUR. | PRICE (BASED ON MIN ORDER) | PRICE IN HK$ (AFTER CONVERSION) | REQUIRE EVALUATION? (Y/N) | ||
| 2 | AIRGATE ENGINEERING (HK) LTD | SERVICE | ONE YEAR MAINTENANCE SERVICE CONTRACT OF THE CLEANROOM FOR IVF CENTRE (HK) LTD 1/1/2022 to 31/12/2022 HK$55,500/ 1 year maintenance service HK$18,500/ per time | MAINTENANCE (LAB) | 1 | 1 | year | $58,000.00 | HK$ | 58000 | 58,000.00 | N | ||
| 3 | AIRGATE ENGINEERING (HK) LTD | SERVICE | ONE YEAR MAINTENANCE SERVICE CONTRACT (ONE TIME) | MAINTENANCE (LAB) | 1 | 1 | year | $18,500.00 | HK$ | 18500 | 18,500.00 | Y | ||
| 4 | AIRGATE ENGINEERING (HK) LTD | SUPPLY AND REPLACE POWER SUPPLY MODULE | MAINTENANCE (LAB) | 1 | 1 | time | $4,000.00 | HK$ | 4000 | 4,000.00 | Y | |||
Items | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H2:H4 | H2 | =IF(C2<>"",K2/F2,"") |
| K2:K4 | K2 | =IFERROR(IF(I2="HK$",J2,J2*10),"") |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| Currency | =OFFSET(Items!$I$1,1,,COUNTA(Items!$J$2:$J$999)-1,1) | K2 |
| ItemName | =OFFSET(Items!$C$2,1,,COUNTA(Items!$C$2:$C$999)-1,1) | H3 |
