I am not an advanced user of Excel and am VERY new to VBA. My only programming experience is 2 C# classes in college. That being said, go easy on me ;)
I am working on a team that audits military bases for energy conservation projects. I am trying to revise a workbook that is currently used to document HVAC equipment in all the buildings on the base. Each building has a separate sheet named after the building number. Each sheet uses the same table and format.
My biggest hurdle was creating a Bill of Materials page that would go through each sheet and count all the parts needed to order. Each sheet can have any combination of parts and quantities so I figured the best way was to loop through each item on a master list and count all the instances in each sheet. As such I have ended up with several nested loops which takes a while to run with a test of only 9 sheets. The code works which is most important to me, but I'm hooked now and want to learn how to make it better. I have picked up a book on VBA and plan on looking into arrays and how they might help. I just wanted to see if anyone could give me some pointers based on what I have now.
Private Sub GenerateBOM_Click() 'generating a bill of materials with data from templated tables on separate sheets. Part order and quantity can change on each sheet. Sheets are named after 'building numbers which could include letters so couldn't find a better way of excluding the summary and data sheets. Wanted to allow for slight table 'structure changes so attempted to locate everything by names. Dim ws As Worksheet Dim tbl As ListObject Dim wsBOM As Worksheet Dim tblBOM As ListObject Dim row As range Dim searchRow As range Dim rowCount As Long Dim partCount As Long Dim totalCount As Long Dim partQty As Long Set wsBOM = Worksheets("Bill of Materials") Set tblBOM = wsBOM.ListObjects("BOM") Application.ScreenUpdating = False For Each row In tblBOM.ListColumns("Part Number").DataBodyRange.Rows rowCount = row.row - tblBOM.HeaderRowRange.row 'getting index of the row being searched. Tried to use ListRow but couldn't figure it out with the overall search totalCount = 0 For Each ws In ThisWorkbook.Worksheets 'Loop through all sheets in a workbook If ws.Name <> "Cover" And ws.Name <> "Building List" And ws.Name <> "Data" And ws.Name <> "Building Template" And ws.Name <> "Parts" And ws.Name <> "Bill of Materials" Then For Each tbl In ws.ListObjects 'Loop through all table on a sheet For Each searchRow In tbl.ListColumns("Part Number").DataBodyRange.Rows 'Loop through all part number rows on table partQty = 0 partQty = tbl.ListColumns("Qty").DataBodyRange(searchRow.row - tbl.HeaderRowRange.row) 'getting index of the row being searched to find per sheet part qty partCount = (Application.WorksheetFunction.CountIf(searchRow, row) * partQty) totalCount = totalCount + partCount tblBOM.ListColumns("Project Totals").DataBodyRange.Cells(rowCount).Value = totalCount 'writing total to bill of materials sheet at index of searched part number Next searchRow Next tbl End If Next ws Next row Application.ScreenUpdating = True End Sub