Hi there,
I am trying to figure out an elegant VBA method to insert certain number of rows with added data based on conditions identified in different sheet.
Data Sheet:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]Country
[/TD]
[TD]Id
[/TD]
[TD]Deadline
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[/TR]
[TR]
[TD]Flat
[/TD]
[TD]Germany
[/TD]
[TD]234567
[/TD]
[TD]02/02/2018
[/TD]
[/TR]
[TR]
[TD]Shed
[/TD]
[TD]Poland
[/TD]
[TD]543212
[/TD]
[TD]03/03/2018
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Finland
[/TD]
[TD]568445
[/TD]
[TD]02/02/2018
[/TD]
[/TR]
[TR]
[TD]Flat
[/TD]
[TD]Spain
[/TD]
[TD]958476
[/TD]
[TD]01/01/2018
[/TD]
[/TR]
[TR]
[TD]Shed
[/TD]
[TD]France
[/TD]
[TD]476940
[/TD]
[TD]04/04/2018
[/TD]
[/TR]
</tbody>[/TABLE]
Requirement Sheet:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]House
[/TD]
[TD]Weeks
[/TD]
[TD][/TD]
[TD]Flat
[/TD]
[TD]Weeks
[/TD]
[TD][/TD]
[TD]Shed
[/TD]
[TD]Weeks
[/TD]
[/TR]
[TR]
[TD]Task1
[/TD]
[TD]-6
[/TD]
[TD][/TD]
[TD]Task1
[/TD]
[TD]-4
[/TD]
[TD][/TD]
[TD]Task1
[/TD]
[TD]-4
[/TD]
[/TR]
[TR]
[TD]Task2
[/TD]
[TD]-5
[/TD]
[TD][/TD]
[TD]Task2
[/TD]
[TD]-3
[/TD]
[TD][/TD]
[TD]Task2
[/TD]
[TD]-3
[/TD]
[/TR]
[TR]
[TD]Task3
[/TD]
[TD]-3
[/TD]
[TD][/TD]
[TD]Task3
[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]Task3
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Task4
[/TD]
[TD]-3
[/TD]
[TD][/TD]
[TD]Task4
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]Task4
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Task5
[/TD]
[TD]-1
[/TD]
[TD][/TD]
[TD]Task5
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Task6
[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Task7
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Task8
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Task9
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Task10
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Task List Sheet
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]Country
[/TD]
[TD]Id
[/TD]
[TD]Deadline
[/TD]
[TD]Task Name
[/TD]
[TD]Task Date
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task1
[/TD]
[TD]20/11/2017
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task2
[/TD]
[TD]27/11/2017
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task3
[/TD]
[TD]11/12/2017
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task4
[/TD]
[TD]11/12/2017
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task5
[/TD]
[TD]25/12/2017
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task6
[/TD]
[TD]01/01/2018
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task7
[/TD]
[TD]08/01/2018
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task8
[/TD]
[TD]22/01/2018
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task9
[/TD]
[TD]29/01/2018
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task10
[/TD]
[TD]29/01/2018
[/TD]
[/TR]
</tbody>[/TABLE]
Essentially I want to accomplish the following:
1. Evaluate row on Data sheet.
2. Compare Type value in row, then look up that type on Requirements sheet and identify the number of tasks
3. Insert the number of rows into Task List sheet by copying all the cells in that row
4. Append Task Name and Task Date at the end of each row.
Task Date is the number of work weeks before (negative) or after (positive) the Deadline.
5. Return to Data sheet and perform same exercise with next row etc.
Example:
House has 10 tasks so Task List sheet should have 10 rows for each respective row from Data sheet that has House specified.
Any ideas anyone?
Thanks in advance!
I am trying to figure out an elegant VBA method to insert certain number of rows with added data based on conditions identified in different sheet.
Data Sheet:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]Country
[/TD]
[TD]Id
[/TD]
[TD]Deadline
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[/TR]
[TR]
[TD]Flat
[/TD]
[TD]Germany
[/TD]
[TD]234567
[/TD]
[TD]02/02/2018
[/TD]
[/TR]
[TR]
[TD]Shed
[/TD]
[TD]Poland
[/TD]
[TD]543212
[/TD]
[TD]03/03/2018
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Finland
[/TD]
[TD]568445
[/TD]
[TD]02/02/2018
[/TD]
[/TR]
[TR]
[TD]Flat
[/TD]
[TD]Spain
[/TD]
[TD]958476
[/TD]
[TD]01/01/2018
[/TD]
[/TR]
[TR]
[TD]Shed
[/TD]
[TD]France
[/TD]
[TD]476940
[/TD]
[TD]04/04/2018
[/TD]
[/TR]
</tbody>[/TABLE]
Requirement Sheet:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]House
[/TD]
[TD]Weeks
[/TD]
[TD][/TD]
[TD]Flat
[/TD]
[TD]Weeks
[/TD]
[TD][/TD]
[TD]Shed
[/TD]
[TD]Weeks
[/TD]
[/TR]
[TR]
[TD]Task1
[/TD]
[TD]-6
[/TD]
[TD][/TD]
[TD]Task1
[/TD]
[TD]-4
[/TD]
[TD][/TD]
[TD]Task1
[/TD]
[TD]-4
[/TD]
[/TR]
[TR]
[TD]Task2
[/TD]
[TD]-5
[/TD]
[TD][/TD]
[TD]Task2
[/TD]
[TD]-3
[/TD]
[TD][/TD]
[TD]Task2
[/TD]
[TD]-3
[/TD]
[/TR]
[TR]
[TD]Task3
[/TD]
[TD]-3
[/TD]
[TD][/TD]
[TD]Task3
[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]Task3
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Task4
[/TD]
[TD]-3
[/TD]
[TD][/TD]
[TD]Task4
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]Task4
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Task5
[/TD]
[TD]-1
[/TD]
[TD][/TD]
[TD]Task5
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Task6
[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Task7
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Task8
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Task9
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Task10
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Task List Sheet
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]Country
[/TD]
[TD]Id
[/TD]
[TD]Deadline
[/TD]
[TD]Task Name
[/TD]
[TD]Task Date
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task1
[/TD]
[TD]20/11/2017
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task2
[/TD]
[TD]27/11/2017
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task3
[/TD]
[TD]11/12/2017
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task4
[/TD]
[TD]11/12/2017
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task5
[/TD]
[TD]25/12/2017
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task6
[/TD]
[TD]01/01/2018
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task7
[/TD]
[TD]08/01/2018
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task8
[/TD]
[TD]22/01/2018
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task9
[/TD]
[TD]29/01/2018
[/TD]
[/TR]
[TR]
[TD]House
[/TD]
[TD]Austria
[/TD]
[TD]123456
[/TD]
[TD]01/01/2018
[/TD]
[TD]Task10
[/TD]
[TD]29/01/2018
[/TD]
[/TR]
</tbody>[/TABLE]
Essentially I want to accomplish the following:
1. Evaluate row on Data sheet.
2. Compare Type value in row, then look up that type on Requirements sheet and identify the number of tasks
3. Insert the number of rows into Task List sheet by copying all the cells in that row
4. Append Task Name and Task Date at the end of each row.
Task Date is the number of work weeks before (negative) or after (positive) the Deadline.
5. Return to Data sheet and perform same exercise with next row etc.
Example:
House has 10 tasks so Task List sheet should have 10 rows for each respective row from Data sheet that has House specified.
Any ideas anyone?
Thanks in advance!
Last edited: