VBA: List Row in Separate Sheet if multiple criteria's are met and more! Need help!

[tommiexboi]

Hello!

Any guidance and help will be GREATLY appreciated!
I have written out what I need (with examples) to give you a clear picture on what I'm trying to accomplish. I hope it helps.

Scenario
I have a "revenue forecast" sheet that shows Customer, Item, Quantity and Average Selling Price (ASP) by Month. If the user enter's a Quantity they must enter a Price for each month. However due to the sheer amount of items the user might accidentally forget to enter the ASP which can alter the forecast drastically.

Example:
"Revenue Forecast"
[TABLE="class: grid, width: 656"]
<colgroup><col><col><col><col span="2"><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]QTY[/TD]
[TD="colspan: 4"]ASP[/TD]
[/TR]
[TR]
[TD]Customer[/TD]
[TD]Item Number[/TD]
[TD]Oct_17[/TD]
[TD]Nov_17[/TD]
[TD]Dec_17[/TD]
[TD]Jan_18[/TD]
[TD]Oct_17[/TD]
[TD]Nov_17[/TD]
[TD]Dec_17[/TD]
[TD]Jan_18[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Toy1[/TD]
[TD]3,000[/TD]
[TD]3,000[/TD]
[TD]2,800[/TD]
[TD]3,000[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD]$1.00[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Toy2[/TD]
[TD]7,000[/TD]
[TD]7,000[/TD]
[TD]4,700[/TD]
[TD]6,000[/TD]
[TD]$2.00[/TD]
[TD]$2.00[/TD]
[TD]$2.00[/TD]
[TD]$2.00[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Toy3[/TD]
[TD]1,200[/TD]
[TD]750[/TD]
[TD]800[/TD]
[TD]750[/TD]
[TD]$3.00[/TD]
[TD]$3.00[/TD]
[TD]$3.00[/TD]
[TD]$3.00[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Toy4[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]1,000[/TD]
[TD]1,500[/TD]
[TD]$4.00[/TD]
[TD]$4.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
</tbody>[/TABLE]


Goal
1) I want to create a separate sheet called "ASP Validate" where in this sheet the user will click a button and a macro will loop and list out the Customer, Item, and Month in which the user forgot to enter the ASP where there is a QTY for each month (The amount of rows will vary).

Example:
"ASP Validate"[TABLE="class: grid, width: 424"]
<colgroup><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]ASP[/TD]
[/TR]
[TR]
[TD]Customer[/TD]
[TD]Item Number[/TD]
[TD]Oct_17[/TD]
[TD]Nov_17[/TD]
[TD]Dec_17[/TD]
[TD]Jan_18[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Toy1[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD]$1.00[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Toy4[/TD]
[TD]$4.00[/TD]
[TD]$4.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
</tbody>[/TABLE]

2) I also want the user to enter the ASP into the "ASP Validate" sheet and click a button and the missing ASP will be entered into the "revenue forecast" sheet.

Example:
"ASP Validate"
[TABLE="class: grid, width: 424"]
<colgroup><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]ASP[/TD]
[/TR]
[TR]
[TD]Customer[/TD]
[TD]Item Number[/TD]
[TD]Oct_17[/TD]
[TD]Nov_17[/TD]
[TD]Dec_17[/TD]
[TD]Jan_18[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Toy1[/TD]
[TD]$1.00[/TD]
[TD]$1.00[/TD]
[TD]$1.00[/TD]
[TD]$1.00[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Toy4[/TD]
[TD]$4.00[/TD]
[TD]$4.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00
[/TD]
[/TR]
</tbody>[/TABLE]

**Click Button**

"Revenue Forecast"
[TABLE="class: grid, width: 656"]
<colgroup><col><col><col><col span="2"><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]QTY[/TD]
[TD="colspan: 4"]ASP[/TD]
[/TR]
[TR]
[TD]Customer[/TD]
[TD]Item Number[/TD]
[TD]Oct_17[/TD]
[TD]Nov_17[/TD]
[TD]Dec_17[/TD]
[TD]Jan_18[/TD]
[TD]Oct_17[/TD]
[TD]Nov_17[/TD]
[TD]Dec_17[/TD]
[TD]Jan_18[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Toy1[/TD]
[TD]3,000[/TD]
[TD]3,000[/TD]
[TD]2,800[/TD]
[TD]3,000[/TD]
[TD]$1.00[/TD]
[TD]$1.00[/TD]
[TD]$1.00[/TD]
[TD]$1.00[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Toy2[/TD]
[TD]7,000[/TD]
[TD]7,000[/TD]
[TD]4,700[/TD]
[TD]6,000[/TD]
[TD]$2.00[/TD]
[TD]$2.00[/TD]
[TD]$2.00[/TD]
[TD]$2.00[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Toy3[/TD]
[TD]1,200[/TD]
[TD]750[/TD]
[TD]800[/TD]
[TD]750[/TD]
[TD]$3.00[/TD]
[TD]$3.00[/TD]
[TD]$3.00[/TD]
[TD]$3.00[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Toy4[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]1,000[/TD]
[TD]1,500[/TD]
[TD]$4.00[/TD]
[TD]$4.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
</tbody>[/TABLE]


3) Once, they click a button that line will disappear from the row

Example:
"ASP Validate:[TABLE="class: grid, width: 424"]
<colgroup><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]ASP[/TD]
[/TR]
[TR]
[TD]Customer[/TD]
[TD]Item Number[/TD]
[TD]Oct_17[/TD]
[TD]Nov_17[/TD]
[TD]Dec_17[/TD]
[TD]Jan_18[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Toy4[/TD]
[TD]$4.00[/TD]
[TD]$4.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
</tbody>[/TABLE]



The amount of rows will vary and will get larger over time. I'm trying to make this as user friendly as possible.

Any help will be greatly appreciated!!

Thank you!

 

[tommiexboi]

Any help will be greatly appreciated!