Problem Statement

For Assignment $5,$ you will start with a bunch of orders that have recently been placed for your publishing company. Each week or so you receive an Excel file with all the orders, which include the Date Ordered, Store, Title of the book, and Quantity. The starter file looks like this:

Goal #$1$: Data cleaning and identification of incomplete orders

Your first goal is to clean up the data. First, you want to remove any formatting in the table. For example, you want to eliminate the red font color in rows $9$ and $34$ and the bold formatting in rows $9$ and $27.$ You should set up your subroutine such that it will adapt to $*$any$*$ order sheet $$ formatting could be in any cell, so make sure you are eliminating any formatting in all cells. Furthermore, the number of orders in subsequent order sheets could be different; therefore, your sub needs to adapt to the number of orders and automatically adjust.

Another aspect of data cleaning is to remove entirely any rows that have a missing Store. If the Title or Quantity is missing, it$$s fairly easy to follow up with the Store to let them know that their order was incomplete $$ in fact, this is one of the main aspects of this assignment, as you$$ll see below. However, if the Store is missing, then it$$s impossible to follow up$,$ so any rows with missing $($blank$)$ Store should be eliminated entirely as part of the data cleaning process.

In the starter file, you should place the code for the above cleaning process into the first part of the FormatAndIncompleteOrders sub $($which is linked to the $$FORMAT & GENERATE INCOMPLETE ORDERS REPORT$).$

Within the FormatAndIncompleteOrders sub should also be code that will generate a report of any incomplete orders. This means that any rows that have either the Title or Quantity missing $($blank$)$ should be copied over to the $$Incomplete Orders$$ sheet. These rows would also be eliminated from the original data on the $$New Orders$$ sheet.

IMPORTANT:

Depending upon how you do things, you may end up inadvertently changing the format of the Date Ordered column to general $($it$$ll just be a serial number, like $44805).$ If this happens $($it probably will$),$ then you can record a macro to reformat the dates to Short Date format and implement this code into your sub.

Make sure that you are NOT deleting the column headers $($Date Ordered$,$Store$,$ etc.$)!$

The Reset sub is only used to reset the ORIGINAL data. You should NOT call $($refer to$)$ the Reset sub from within your FormatAndIncompleteOrders sub! If you are having issues with the grading file, you might check to make sure that you are not calling the Reset sub in your FormatAndIncompleteOrders sub.

The second main objective of this assignment is to generate a Store report from the Store that$$s selected from the drop$-$down menu in cell H$10.$ After a Store is selected in the drop$-$down list, the user can press the GENERATE STORE REPORT button, which runs the Report sub, the data will be filtered by column B$,$ and only those rows that match the Store in cell H$10$ would be selected$/$copied and pasted over to the $$Report$$ sheet. Note that you are NOT removing the rows from the original data in the $$New Orders$$ sheet but are just copying over those orders that correspond to the Store in cell H$10.$

This is my code :

Sub FormatAndIncompleteOrders$()$

'This sub is run using the "FORMAT & GENERATE INCOMPLETE ORDERS REPORT" button

Reset

Dim nr As Integer

nr $=$ WorksheetFunction.CountA$($Columns$("$A:A$"))$

Range$("$A$3").$CurrentRegion.Rows$("4$:$"$ & nr$).$Style $=$ "Normal"

Range$("$A$4$:A$"$ & nr $+1).$Select

Selection.NumberFormat $="$m$/$d$/$yyyy$"$

Range$("$A$3").$AutoFilter Field:$=2,$ Criteria$1$:$=""$

Range$("$A$3").$CurrentRegion.Offset$(1).$EntireRow.Delete

Range$("$A$3").$AutoFilter

Range$("$A$3").$AutoFilter Field:$=3,$ Criteria$1$:$=""$

Range$("$A$3").$CurrentRegion.Copy Sheets$("$Incomplete Orders"$).$Range$("$A$1")$

Sheets$("$Incomplete Orders"$).$Columns$("$A:D$").$EntireColumn.AutoFit

Range$("$A$3").$CurrentRegion.Offset$(1).$EntireRow.Delete

Range$("$A$3").$AutoFilter

End Sub

Sub Report$()$

'This sub is run using the "GENERATE STORE REPORT" button

Reset

Dim nr As Integer

nr $=$ WorksheetFunction.CountA$($Columns$("$A:A$"))$

Range$("$A$3").$AutoFilter Field:$=2,$ Criteria$1$:$=$Range$("$H$10")$

Range$("$A$3").$CurrentRegion.Copy Sheets$("$Report$").$Range$("$A$1")$

Sheets$("$Report$").$Columns$("$A:D$").$EntireColumn.AutoFit

Range$("$A$3").$AutoFilter

Sheets$("$Report$").$Select

End Sub

Sub Reset$()$

$\text{'}$Do NOT modify or delete this sub!

Sheets$("$Original Data"$).$Columns$("$A:D$").$Copy Sheets$("$New Orders"$).$Columns$("$A:D$")$

Sheets$("$Report$").$Cells.Clear

Sheets$("$Incomplete Orders"$).$Cells.Clear

End Sub

The Grading file says that my FormatAndIncompleteOrders sub doesn't work when another set of data is used. All other subs are working properly. Kindly take a look at my FormatAndIncompletteOrders sub and help me out $.$

Thank you.