'***********************************************************************
'*Fishbowl Inventory Valuation Summary Report App                      *
'*Written By: Profitwyse                                               *
'*Version: 1.2                                                         *
'***********************************************************************
Option Explicit
'***********************************************************************
'*                         MAIN PROCEDURE                              *
'***********************************************************************
Sub MainProc()
    'Declare character strings
    Dim gcInvGroups()               As String           'Array for Inventory Groups
    Dim gcCurInvGroup               As String           'Scalar for current Inventory Group
    Dim gcCurInvLocation            As String           'Scalar for current Inventory Location
    
    'Declare Numerics
    Dim gnRowCount                  As Integer
    Dim gnI0                        As Integer
    Dim gnMaxRow                    As Integer
    Dim gnLocationGrpNum            As Integer          'This is used to track the number of Location Groups
    Dim gnInvByPnRow                As Integer          'This tracks the current row to write to on Inv-By-PN
    'Declare Ranges
    Dim grInvValImport              As Range            'Starting point for Valuation Report
    Dim grInvByPN                   As Range            'Starting point to write data on Inv-By-PN worksheet
    'Establish Ranges
    Set grInvValImport = ActiveWorkbook.Sheets("Inv-Val-Import").Range("A6")
    Set grInvByPN = ActiveWorkbook.Sheets("Inv-By-PN").Range("A2")
    'Set gcCurInvGroup = blank for first cycle through loop
    gcCurInvGroup = ""
    'Set gcCurInvLocation = blank for first cycle through loop also
    gcCurInvLocation = ""
    'Get the number of Location Groups on InventoryGroups (name)
    gnLocationGrpNum = Worksheets("InvGroups").Range("InventoryGroups").Rows.Count
    'Determine number of rows in report
    gnRowCount = 0                  'Set row counter to 0
    For gnI0 = 0 To 3000
        'Check to see if we're at the last record in the file
        If grInvValImport.Offset(gnI0, 0) = "Grand Total:" Then
            Exit For
        End If
        'Set gnMaxRow to last potentially valid row
        gnMaxRow = gnI0 - 1
    Next
    'Set Inv-By-PN row counter to 0
    gnInvByPnRow = 0
    'Clear the contents on Inv-By-PN, excluding the headers, before appending data
    Worksheets("Inv-By-PN").Range("A2:Z10000").ClearContents
    Do While gnRowCount <= gnMaxRow
    'Check to see there is something in column "A", either an Inventory Group or location and act accordingly
        If grInvValImport.Offset(gnRowCount, 0) <> "" Then
            'Check to see if the new item in column A is an Inventory Group.  If so update gcCurInvGroup
            'Otherwise, it must be a new location, so update gcCurInvLocation
            'Note that the searchin of InventoryGroups starts with the header "Groups"???
            For gnI0 = 0 To gnLocationGrpNum
                If Range("InventoryGroups").Cells(gnI0).Value = grInvValImport.Offset(gnRowCount, 0) Then
                    'Reset the Inventory Group scalar and exit for
                    gcCurInvGroup = grInvValImport.Offset(gnRowCount, 0)
                    Exit For
                End If
            Next
            'If we exited the for before getting to the last Inventory Group, then we have a new inventory group; otherwise
            'we have a new inventory location, so go head and reset the inventory location
            If gnI0 = gnLocationGrpNum + 1 Then
                'if gnI0 = gnLocatnoiGrpNum then we didn't find the latest new item in the Location Group list
                'So go ahead and reset the location
                gcCurInvLocation = grInvValImport.Offset(gnRowCount, 0)
            End If
        Else                        'if we reach this else, then there is nothing in column A meaning this could be an inventory record
            If grInvValImport.Offset(gnRowCount, 1) <> "" Then
                grInvByPN.Offset(gnInvByPnRow, 0) = gcCurInvGroup
                grInvByPN.Offset(gnInvByPnRow, 1) = gcCurInvLocation
                grInvByPN.Offset(gnInvByPnRow, 2) = grInvValImport.Offset(gnRowCount, 1)        'Fetch the Part #
                grInvByPN.Offset(gnInvByPnRow, 3) = grInvValImport.Offset(gnRowCount, 2)        'Fetch the Description
                grInvByPN.Offset(gnInvByPnRow, 4) = grInvValImport.Offset(gnRowCount, 10)       'Fetch the Quantity
                grInvByPN.Offset(gnInvByPnRow, 6) = grInvValImport.Offset(gnRowCount, 15)       'Fetch the Extended Cost
                grInvByPN.Offset(gnInvByPnRow, 7) = grInvValImport.Offset(gnRowCount, 12)       'Fetch the UOM
                'Calculate the unit cost
                grInvByPN.Offset(gnInvByPnRow, 5) = Round(grInvByPN.Offset(gnInvByPnRow, 6) / grInvByPN.Offset(gnInvByPnRow, 4), 5)
                gnInvByPnRow = gnInvByPnRow + 1
            End If
        End If
        gnRowCount = gnRowCount + 1
    Loop
    'MsgBox (gnI0)
    
End Sub