CobusVanWyk
New Member
- Joined
- Jun 14, 2021
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
Hi Everyone
I have tried to find a solution in the already asked questions section but to no avail... I need some help please. I will explain to the best of my ability. I'm new to VBA.
I am trying to write a VBA code to perform the mentioned task in the title. Here is some information:
I have created a workbook containing our stock codes in the warehouse in sheet 1. So it contains all stock items with a specified code in Column C of sheet 1. It also contains quantities and pricing info etc.
In sheets 2 to 40 in the same workbook, are our companies products. Certain products use certain stock items, but not all. Each product has its own sheet.
Each product sheet (2-40) therefore only contains some of the stock codes found in sheet 1.
I want the code, to look at the stock codes found in the product sheets (sheet 2 - 40). The product codes are also in column C.
If the code is present in Sheet2-40 respectively, I want the values found from sheet 1 (column D,E,J,K) to be copied in sheet 2-40 (Column D,E,F,H) respectively.
Notes:
- The function needs to compare all text codes found in column C2 downwards in sheets 2-40 to column C2 downwards in sheet1, in the same workbook.
- Column C is a stock code but in the same row as the stock code, are various other values of which I only want to copy over certain values in the other sheets, if found.
- The function needs to loop from column C row 2 (row 1 are headers) to the last row in the column that contains a value (in Sheet1).
- Column C will always be used for the check between Sheet1 and Sheet2-40 to see if a matching value exists.
Goal:
- Loop from column/row C2 downwards to the last row that contains a value in sheets 2-40 to see if these values exist in Column/row C2 downwards of Sheet 1.
- If a matching code is found on Sheet2-40 respectively, populate column values D,E,J,K from sheet 1 to column values D,E,F,H in matching row in sheet 2-40. The rows may differ in sheets 2-40.
I apologize if this is unclear, it is hard to convey the information properly without attaching a sample workbook.
I am attaching a mini sheet for Sheet 1 and a product Sheet X
Mini Sheet: Sheet 1
Mini Sheet: Sheet X (2-40)
I have tried to find a solution in the already asked questions section but to no avail... I need some help please. I will explain to the best of my ability. I'm new to VBA.
I am trying to write a VBA code to perform the mentioned task in the title. Here is some information:
I have created a workbook containing our stock codes in the warehouse in sheet 1. So it contains all stock items with a specified code in Column C of sheet 1. It also contains quantities and pricing info etc.
In sheets 2 to 40 in the same workbook, are our companies products. Certain products use certain stock items, but not all. Each product has its own sheet.
Each product sheet (2-40) therefore only contains some of the stock codes found in sheet 1.
I want the code, to look at the stock codes found in the product sheets (sheet 2 - 40). The product codes are also in column C.
If the code is present in Sheet2-40 respectively, I want the values found from sheet 1 (column D,E,J,K) to be copied in sheet 2-40 (Column D,E,F,H) respectively.
Notes:
- The function needs to compare all text codes found in column C2 downwards in sheets 2-40 to column C2 downwards in sheet1, in the same workbook.
- Column C is a stock code but in the same row as the stock code, are various other values of which I only want to copy over certain values in the other sheets, if found.
- The function needs to loop from column C row 2 (row 1 are headers) to the last row in the column that contains a value (in Sheet1).
- Column C will always be used for the check between Sheet1 and Sheet2-40 to see if a matching value exists.
Goal:
- Loop from column/row C2 downwards to the last row that contains a value in sheets 2-40 to see if these values exist in Column/row C2 downwards of Sheet 1.
- If a matching code is found on Sheet2-40 respectively, populate column values D,E,J,K from sheet 1 to column values D,E,F,H in matching row in sheet 2-40. The rows may differ in sheets 2-40.
I apologize if this is unclear, it is hard to convey the information properly without attaching a sample workbook.
I am attaching a mini sheet for Sheet 1 and a product Sheet X
Mini Sheet: Sheet 1
Alti Product and Pricing sheet - COBUS TEST.xlsx | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Type | Supplier | StockCode | Description | Reorder Pt (Min) | QtyOnHand | QtyOnSalesOrder | QtyOnPO | Available | UnitPriceUSD | UnitPriceZAR | ||
2 | ASCEND | (ZAR) RF DESIGN CC | AS-AN001 | Cable Assembly (Taisync) SMA-F RA BLK/H TO MMCX-M, RG316/U, 200mm(CA20FRABK25MDM2) | 1,00 | 2 | 1 | 0 | 1 | R 115,56 | |||
3 | ASCEND | (ZAR) RF DESIGN CC | AS-AN002 | Cable Assembly (Taisync) SMA-F RA BLK/H to MMCX-M, RG316/U, 400mm(CA20FRABK25MDM4) | 1,00 | 2 | 1 | 0 | 1 | R 120,19 | |||
4 | ASCEND | (USD) SOUTH WEST ANTENNAS | AS-AN003 | Omni Antenna, Half Wave Dipole 2.1 - 2.5 GHz 2.15 dBI (1001-048) | 2,00 | 2 | 2 | 0 | 0 | $ 115,00 | |||
5 | ASCEND | (ZAR) RF DESIGN CC | AS-AN004 | Ascend Taoglas 868 MHz Flex Circuit Antenna, 220mm, MCX-M (FXP280.07.0220M).. | 2,00 | 2 | 0 | 0 | 2 | R 102,98 | |||
6 | ASCEND | (ZAR) RF DESIGN CC | AS-AN005 | Ascend Taoglas 915 MHz Flex Circuit Antenna, 220mm, MCX-M .. | 2,00 | 0 | 0 | 0 | 0 | R 102,98 | |||
7 | ASCEND | (USD) Taisync Technology LLC | AS-AP001 | 2.4G, Eth-in-Eth-out Module with USB2UART adaptor - Airside | 1,00 | 1 | 1 | 0 | 0 | $ 550,00 | |||
8 | ASCEND | (USD) SHENZHEN CAINAN TECHNOLOGY CO., LTD | AS-AP002 | Alti CF Taisync Mount - Airside | 1,00 | 0 | 0 | 0 | 0 | $ 0,00 | |||
9 | ASCEND | (ZAR) The Aviation Shop | AS-AP003 | Remove before Flight (PS35) | -1 | 0 | 0 | -1 | R 0,00 | ||||
10 | ASCEND | (USD) SHENZHEN GREPOW BATTERY CO., LTD | AS-BA26325C | LiPo 2600mAh 3S 25C Gens Ace | 2,00 | 0 | 0 | 0 | 0 | $ 19,49 | |||
11 | ASCEND | (USD) SHENZHEN GREPOW BATTERY CO., LTD | AS-BA33445C | Lipo 3300mAh 4S 45C.. | 4,00 | 8 | 0 | 0 | 8 | $ 24,92 | |||
12 | ASCEND | (ZAR) INTERNAL ALTI R&D | AS-BA352 | Lipo 3500mAh 2S RX GensAce.. | 2,00 | 12 | 1 | 0 | 11 | R 249,18 | |||
13 | ASCEND | (ZAR) JBS ENERGY SOLUTIONS | AS-BA523 | Build 11.1V Li-ion 3S2P 5.6Ah Battery (Datalink).. | 2,00 | 0 | 1 | 1 | 0 | R 895,00 | |||
14 | ASCEND | (ZAR) REBUL | AS-BX001 | Ascend Freight Crate | 1,00 | 1 | 0 | 0 | 1 | R 1 550,00 | |||
15 | ASCEND | (USD) CASTLE | AS-CN001F | Connector Castle Creations 6.5mm Female | 1,00 | -1 | 1 | 0 | -2 | $ 2,65 | |||
16 | ASCEND | (USD) CASTLE | AS-CN001M | Connector Castle Creations 6.5mm Male | 1,00 | -1 | 1 | 0 | -2 | $ 2,65 | |||
17 | ASCEND | (USD) RC TIMER | AS-CN002F | Connector XT60 Female | 4,00 | 11 | 4 | 0 | 7 | $ 0,52 | |||
18 | ASCEND | (USD) RC TIMER | AS-CN002M | Connector XT60 Male | 2,00 | 13 | 2 | 0 | 11 | $ 0,52 | |||
19 | ASCEND | (ZAR) RS COMPONENTS SA | AS-CN003M | Binder 620 3 Way Male w Cable (734-5338).. | 2,00 | 6 | 0 | 0 | 6 | R 190,66 | |||
20 | ASCEND | (ZAR) RS COMPONENTS SA | AS-CN004F | Binder 620 3-way female panel mount connector (468-976) | 6 | 0 | 0 | 6 | R 108,45 | ||||
21 | ASCEND | (ZAR) RS COMPONENTS SA | AS-CN005 | Binder 620 Snap-In IP67 adapter, pluggable ( 468-992).. | 2,00 | 5 | 0 | 0 | 5 | R 54,80 | |||
22 | ASCEND | (USD) RC TIMER | AS-CN006 | Amass AS150 Connector 7mm Gold Plated Spark Free Connector.. | 4,00 | 19 | 12 | 0 | 7 | $ 5,48 | |||
23 | ASCEND | (USD) CASTLE | AS-CN007 | Connector CC Bullet 4mm | 12,00 | 66 | 12 | 0 | 54 | $ 1,98 | |||
24 | ASCEND | (USD) RC TIMER | AS-CN008 | Connector 4.0mm Bullet Male (charge leads) | 8,00 | 72 | 8 | 0 | 64 | $ 0,20 | |||
25 | ASCEND | (ZAR) ALPINE GULF EQUIPMENT | AS-CS001 | Ascend Case | 1,00 | 1 | 0 | 0 | 1 | R 21 782,00 | |||
26 | ASCEND | (ZAR) EPSILON ENGINEERING SERVICES | AS-FR | ALTI Ascend Airframe | 1,00 | 3 | 1 | 0 | 2 | R 106 000,00 | |||
27 | ASCEND | (ZAR) ESHEN & REID | AS-FR001 | Serial Number Plate | 1,00 | 4 | 1 | 0 | 3 | R 25,00 | |||
28 | ASCEND | (ZAR) PRO BOLT | AS-FR002 | M4 Aluminum Dome Head Bolt..M4 x (0.7mm) x 25mm (LFB425) | 4,00 | 0 | 1 | 0 | -1 | R 29,00 | |||
29 | ASCEND | (USD) MACGREGOR INDUSTRIES LIMITED | AS-FU001 | SE Fuel system V2 | 1,00 | 0 | 0 | 0 | 0 | $ 86,62 | |||
30 | ASCEND | (USD) BATTLEFIELD INTERNATIONAL INC. | AS-FU002 | Battlefield Fuel Filler Plug, EL01Y, 02 x 04 RU, Brass/Alum (AA1A1S) | 1,00 | 0 | 1 | 0 | -1 | $ 119,00 | |||
31 | ASCEND | (USD) BATTLEFIELD INTERNATIONAL INC. | AS-FU003 | Battlefield Refuel Valve EL01Y Coupler, 02 x 04 RU, Brass/Alum (AA1A9R) | 1,00 | 0 | 0 | 0 | 0 | $ 154,00 | |||
32 | ASCEND | (USD) BATTLEFIELD INTERNATIONAL INC. | AS-FU004 | Battlefield Inline Fuel Filter Repair Kit (BA7A3A-REP) | 1,00 | 1 | 0 | 0 | 1 | $ 70,00 | |||
33 | ASCEND | (USD) BATTLEFIELD INTERNATIONAL INC. | AS-FU005 | Battlefield Inline Fuel Filter 02x04 REHB Fuel Filter Assembly (BA7A3A) | 1,00 | -1 | 0 | 0 | -1 | $ 109,00 | |||
34 | ASCEND | (USD) BATTLEFIELD INTERNATIONAL INC. | AS-FU006 | Dual Panel Mount Hose Barb, 2 x 2.3mm Hose - Custom Made Airtube Connectors (BA6A7B) | 2,00 | 6 | 0 | 0 | 6 | $ 9,99 | |||
35 | ASCEND | (ZAR) Aluma Tig Worx | AS-FU007 | Ascend Motor Mnt Screw | 4,00 | 24 | 0 | 0 | 24 | R 0,00 | |||
36 | ASCEND | (ZAR) Aluma Tig Worx | AS-FU008 | Header Tank Lower V2 | 1,00 | 0 | 0 | 0 | 0 | R 0,00 | |||
37 | ASCEND | (ZAR) Aluma Tig Worx | AS-FU009 | Header Tank Upper V2 | 1,00 | 0 | 0 | 0 | 0 | R 0,00 | |||
38 | ASCEND | (USD) MACGREGOR INDUSTRIES LIMITED | AS-PP001 | Saito FG-14C (SAT14CFG) | 1,00 | 1 | 1 | 0 | 0 | $ 249,37 | |||
39 | ASCEND | (USD) TIGER MOTOR | AS-PP002 | T-Motor MN501 550KV Custom Motor | 4,00 | 49 | 4 | 0 | 45 | $ 84,90 | |||
40 | ASCEND | (USD) TIGER MOTOR | AS-PP003 | T-Motor Flame 60A ESC | 4,00 | 4 | 4 | 9 | 9 | $ 69,99 | |||
41 | ASCEND | (ZAR) JK PRODUCTS | AS-PP004 | Dubro3 Blade Spinner,2-1/2Inc,Black (DUB546).. | 1,00 | 1 | 1 | 0 | 0 | R 146,00 | |||
42 | ASCEND | (ZAR) JK PRODUCTS | AS-PP005 | Turbo Flow Pro Air Filter 21 (DYN2505) | 1,00 | 0 | 0 | 0 | 0 | R 100,00 | |||
43 | ASCEND | (ZAR) JK PRODUCTS | AS-PP006 | Hangar 9 12V Starter.. | 1,00 | 0 | 0 | 0 | 0 | R 526,00 | |||
44 | ASCEND | (USD) MACGREGOR INDUSTRIES LIMITED | AS-PP007 | Spark Plug (SAIG20120).. | 1,00 | 0 | 0 | 0 | 0 | $ 22,76 | |||
45 | ASCEND | (USD) MACGREGOR INDUSTRIES LIMITED | AS-PP008 | Gasket Kit | 1,00 | 3 | 0 | 0 | 3 | $ 6,99 | |||
46 | ASCEND | (USD) MASTER AIRSCREW | AS-PP009 | Master Airscrew 12*8" Pusher Propeller..(MA.3B12X80R01) | 2,00 | 1 | 1 | 0 | 0 | $ 11,98 | |||
47 | ASCEND | (USD) MASTER AIRSCREW | AS-PP010 | Mater Airscrew CW (MA.3X13X12RB1) | 3,00 | 14 | 0 | 0 | 14 | $ 13,99 | |||
48 | ASCEND | (USD) MASTER AIRSCREW | AS-PP011 | Mater Airscrew CCW (MA.3X13X12NB1) | 3,00 | 14 | 0 | 0 | 14 | $ 14,99 | |||
49 | ASCEND | (ZAR) NELCAM ENGINEERING | AS-PP012 | Ascend Engine Mount | 2,00 | 0 | 0 | 0 | 0 | R 390,00 | |||
50 | ASCEND | (USD) TIGER MOTOR | AS-PP013 | Ascend Prop Adaper CW | 2,00 | 4 | 0 | 0 | 4 | $ 6,00 | |||
51 | ASCEND | (USD) TIGER MOTOR | AS-PP014 | Ascend Prop Adaper CCW | 2,00 | 4 | 0 | 0 | 4 | $ 6,00 | |||
Stock Order |
Mini Sheet: Sheet X (2-40)
Alti Product and Pricing sheet - COBUS TEST.xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | # | Type | StockCode | Description | AS_C_MOQ | UnitPriceZAR | CostInZAR | UnitPriceUSD | CostInUSD | ||
2 | 15 | ASCEND | AS-BX001 | Ascend Freight Crate | 1 | R 1 550,00 | R 1 550,00 | $ 0,00 | |||
3 | 16 | ASCEND | AS-CN007 | Connector CC Bullet 4mm | 12 | R 0,00 | $ 1,98 | $ 23,76 | |||
4 | 17 | ASCEND | AS-CS001 | Ascend Case | 1 | R 21 782,00 | R 21 782,00 | $ 0,00 | |||
5 | 18 | ASCEND | AS-FR | ALTI Ascend Airframe | 1 | R 106 000,00 | R 106 000,00 | $ 0,00 | |||
6 | 19 | ASCEND | AS-FR001 | Serial Number Plate | 1 | R 25,00 | R 25,00 | $ 0,00 | |||
7 | 20 | ASCEND | AS-FR002 | M4 Aluminum Dome Head Bolt..M4 x (0.7mm) x 25mm (LFB425) | 4 | R 29,00 | R 116,00 | $ 0,00 | |||
8 | 21 | ASCEND | AS-FU002 | Battlefield Fuel Filler Plug, EL01Y, 02 x 04 RU, Brass/Alum (AA1A1S) | 1 | R 0,00 | $ 119,00 | $ 119,00 | |||
9 | 22 | ASCEND | AS-FU003 | Battlefield Refuel Valve EL01Y Coupler, 02 x 04 RU, Brass/Alum (AA1A9R) | 1 | R 0,00 | $ 154,00 | $ 154,00 | |||
10 | 23 | ASCEND | AS-FU005 | Battlefield Inline Fuel Filter 02x04 REHB Fuel Filter Assembly (BA7A3A) | 1 | R 0,00 | $ 109,00 | $ 109,00 | |||
11 | 24 | ASCEND | AS-FU006 | Dual Panel Mount Hose Barb, 2 x 2.3mm Hose - Custom Made Airtube Connectors (BA6A7B) | 2 | R 0,00 | $ 9,99 | $ 19,98 | |||
12 | 25 | ASCEND | AS-PP001 | Saito FG-14C (SAT14CFG) | 1 | R 0,00 | $ 249,37 | $ 249,37 | |||
13 | 1 | ASCEND | AS-PP002 | T-Motor MN501 550KV Custom Motor | 4 | R 0,00 | $ 84,90 | $ 339,60 | |||
14 | 2 | ASCEND | AS-PP003 | T-Motor Flame 60A ESC | 4 | R 0,00 | $ 69,99 | $ 279,96 | |||
15 | 3 | ASCEND | AS-PP004 | Dubro3 Blade Spinner,2-1/2Inc,Black (DUB546).. | 1 | R 146,00 | R 146,00 | $ 0,00 | |||
16 | 4 | ASCEND | AS-PP009 | Master Airscrew 12*8" Pusher Propeller..(MA.3B12X80R01) | 1 | R 0,00 | $ 11,98 | $ 11,98 | |||
17 | 5 | ASCEND | AS-PP012 | Ascend Engine Mount | 2 | R 390,00 | R 780,00 | $ 0,00 | |||
18 | 6 | ASCEND | AS-PP015 | Ascend ESC Spacer | 4 | R 0,00 | $ 3,00 | $ 12,00 | |||
19 | 7 | ASCEND | AS-PP016 | T-Motor P14*4.8 (CW & CCW) | 2 | R 0,00 | $ 30,90 | $ 61,80 | |||
20 | 8 | ASCEND | AS-RU001 | Grommet EDPM 27x16x22x1 | 2 | R 4,75 | R 9,50 | $ 0,00 | |||
21 | 9 | ASCEND | AS-SV001 | MKS HV6130 - Control surface servos | 3 | R 63,75 | R 191,25 | $ 0,00 | |||
22 | 10 | ASCEND | AS-SV002 | MKS HV6130H - Throttle Servo | 1 | R 63,75 | R 63,75 | $ 0,00 | |||
Ascend Base |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2 | F2 | ='Stock Order'!K14 |
G2:G22 | G2 | =E2*F2 |
H3 | H3 | ='Stock Order'!J23 |
F4:F7 | F4 | ='Stock Order'!K25 |
H8:H9 | H8 | ='Stock Order'!J30 |
H10:H11 | H10 | ='Stock Order'!J33 |
H12:H14 | H12 | ='Stock Order'!J38 |
F15 | F15 | ='Stock Order'!K41 |
H16 | H16 | ='Stock Order'!J46 |
F17 | F17 | ='Stock Order'!K49 |
H18:H19 | H18 | ='Stock Order'!J52 |
F20 | F20 | ='Stock Order'!K55 |
F21:F22 | F21 | ='Stock Order'!J56 |
I2:I22 | I2 | =E2*H2 |