IC11 In Class Northwinds Product Summary Solution PDF

Preview

Summary

In class macro code for product summaries...

Description

' Kerry Guilliams ' ITM2500 FL19 ' Northwinds Product Detail Summary by Order ‘ *** Note – Modified to include only orders with zero discount Option Explicit

' Option Explicit Forces Declaration of Variables (kinda spell checks)

Sub IC09A_SummaryByProduct() ' Define Workbook, Worksheets and Related Variables Dim GKM_wkBook As Workbook Dim GKM_datSheet As Worksheet Dim GKM_rptSheet As Worksheet Dim datSheetName As String Dim rptSheetName As String Dim intRptRow As Integer Dim intRptCol As Integer Dim intDatRow As Integer Dim intDatCol As Integer ' Dim Variables for Sorting Data Dim GKM_dataFirstCell Dim GKM_dataLastCell Dim GKM_sortFirstCell Dim GKM_sortLastCell ' Dim Totals and Categorization Variables Dim dblProductTotal As Double Dim intCurrentProduct As String ' Administrative and Setup (HouseKeeping) datSheetName = "GKM_OrderDetailData" rptSheetName = "GKM_Report" ' Establish the same file as contains this macro as the file the workbook points to Set GKM_wkBook = ThisWorkbook GKM_wkBook.Save ' Delete Report from Any Prior Run and Establish A New (Clean) Report Tab ' If it doesn't exist we would get an error Application.DisplayAlerts = False ' Suppress Error Messages and Warnings

On Error Resume Next ' Continue processing even with errors GKM_wkBook.Worksheets(rptSheetName).Delete Application.DisplayAlerts = True ' Turn Error Messages Back On On Error GoTo 0 ' Do Not Proceed If Error Encountered Set GKM_rptSheet = GKM_wkBook.Worksheets.Add ' Create a new tab and assign it to GKM_rptSheet GKM_rptSheet.Name = rptSheetName ' Change the name of the report tab Set GKM_datSheet = GKM_wkBook.Worksheets(datSheetName) GKM_datSheet.Activate intDatRow = 2 intDatCol = 1 intRptRow = 5 intRptCol = 1 ' Build Header and Column Title Information GKM_rptSheet.Cells(1, 1).Value = "Kerry Guilliams" GKM_rptSheet.Cells(2, 1).Value = "ITM2500 FL19 Northwinds Product Summary" GKM_rptSheet.Cells(4, 1).Value = "Product" GKM_rptSheet.Cells(4, 2).Value = "Total" ' Determine the Boundaries / Size of Data Contained in Data Tab GKM_datSheet.Range("A1").Select GKM_dataFirstCell = ActiveCell.Address Selection.End(xlDown).Select Selection.End(xlToRight).Select GKM_dataLastCell = ActiveCell.Address ' Establish Sort Key Range GKM_datSheet.Range("B1").Select GKM_sortFirstCell = ActiveCell.Address Selection.End(xlDown).Select GKM_sortLastCell = ActiveCell.Address ' Sort the Data Based On Column B ' This is Fixed For Now - We Will Make it Dynamic Later With GKM_datSheet.Sort .SortFields.Clear .SortFields.Add Key:=Range(GKM_sortFirstCell, GKM_sortLastCell), _ SortOn:=xlSortOnValues, Order:=xlAscending .SetRange Range(GKM_dataFirstCell, GKM_dataLastCell) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom

.Apply End With ' Establish Initial Conditions dblProductTotal = 0 intCurrentProduct = GKM_datSheet.Cells(2, 2).Value ' Process Data While GKM_datSheet.Cells(intDatRow, 1).Value > 0 If GKM_datSheet.Cells(intDatRow, 2).Value = intCurrentProduct Then If GKM_datSheet.Cells(intDatRow, 5).Value = 0 Then dblProductTotal = dblProductTotal + GKM_datSheet.Cells(intDatRow, 3).Value * GKM_datSheet.Cells(intDatRow, 4).Value End If Else ' New Order in Data Sheet - So Write Out Current Order Results First GKM_rptSheet.Cells(intRptRow, 1).Value = intCurrentProduct GKM_rptSheet.Cells(intRptRow, 2).Value = dblProductTotal ' Reset Initial Conditions for Next Order intCurrentProduct = GKM_datSheet.Cells(intDatRow, 2).Value If GKM_datSheet.Cells(intDatRow, 5).Value = 0 Then dblProductTotal = GKM_datSheet.Cells(intDatRow, 3).Value * GKM_datSheet.Cells(intDatRow, 4).Value Else dblProductTotal = 0 End If ' Move Report Row to Next Row intRptRow = intRptRow + 1 End If intDatRow = intDatRow + 1 Wend ' Write the Last Order Information GKM_rptSheet.Cells(intRptRow, 1).Value = intCurrentProduct GKM_rptSheet.Cells(intRptRow, 2).Value = dblProductTotal

' ********************************************************************* GKM_rptSheet.Activate ' Determine the Boundaries / Size of Data Contained in Data Tab GKM_rptSheet.Range("A4").Select

GKM_dataFirstCell = ActiveCell.Address Selection.End(xlDown).Select Selection.End(xlToRight).Select GKM_dataLastCell = ActiveCell.Address ' Establish Sort Key Range GKM_rptSheet.Range("B4").Select GKM_sortFirstCell = ActiveCell.Address Selection.End(xlDown).Select GKM_sortLastCell = ActiveCell.Address ' Sort the Data Based On Column B ' This is Fixed For Now - We Will Make it Dynamic Later With GKM_rptSheet.Sort .SortFields.Clear .SortFields.Add Key:=Range(GKM_sortFirstCell, GKM_sortLastCell), _ SortOn:=xlSortOnValues, Order:=xlAscending .SetRange Range(GKM_dataFirstCell, GKM_dataLastCell) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .Apply End With ' ********************************************************************* GKM_rptSheet.Activate GKM_rptSheet.Range("A1").Select...