Nested loop inserting new row and paste values depending on mulitple creteria

Brutusar

Board Regular
Joined
Nov 23, 2019
Messages
166
Office Version
  1. 365
Platform
  1. Windows
Hi, I am trying to create a code, and slowly realizes it is out of my league. At least parts of it. As I manage to confuse myself even by writing the description I will will completely understand if nobody wish to touch this, but I will give it a try.


There are two tables, A and B.

The purpose of the code is to loop thru table A, looking for parameters in table B. If one or more parameters are found (depending description below), a new row is created in table B and values from the row being looped at the moment in table A should be pasted in the new row.

Each row in table A need to be validated to each row in table B.

Parameters:

The columns in table A that need to be checked against table B are:

DepA
DepA_CC
DepB
DepB_CC
Unit


The cells in tbl A col DepA and DepB can have three main values

A - A five letter code, if present it needs to be mached exactly
B - empty = to be ignored
C - * = valid for any five letter code

The cells in tbl A col DepA_CC and DepB_CC can have two main values
A - a two letter code. These two letters will correspond to the first two letters in col DepA and DepB
B - empty = to be ignored

The cells in tbl A col Unit can have main values
A - a value (if present it need to be identical to the value in the row looped in table b col Unit)
B - * = valid for any value in the row looped in table b col Unit

How to use the parameters above:

IF tbl A, Col DepA
AND/OR
Col tbl A, DepA_CC
AND
tbl A, Col DepB
AND/OR
tbl A, Col DepB_CC
AND
tbl A, COl Unit
=
tbl B, Col DepA
AND/OR tbl B Col DepB
AND/OR tbl B Col Unit
THEN
create new row in table B and paste values from the row being looped.


Values from these columns should be pasted from table A to table B:
Item copy to Item
Price copy to Price
Currency copy to Currency
Unit2 copy to Unit
Payee copy to Payee


VBA EX.xlsx
F
23
Sheet1
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,213,585
Messages
6,114,514
Members
448,575
Latest member
hycrow

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
Back
Top