Inventory Mgmt Buttons - VBA code showing no errors but not working as intended

[texexcel]

Hello and thank you for taking a look at my Excel VBA inquiry!

I am working on creating an inventory management system using Excel and VBA code for several buttons. For a bit of background information, our company manufactures vacuum tube audio amplifiers and keeps hundreds of components in stock. We created a Bill of Materials [BOM] for each product and are trying to implement a system that allows the user to click a button to build the selected product - i.e. pull each component listed on the specified BOM from the master inventory spreadsheet, deduct the BOM quantity from the current inventory quantity, and in turn this would reflect the updated overall inventory value. It also has the functionality to trigger whenever a component needs to be reordered from a preset Reorder level. My issue arises in creating the code for the build buttons. I have a Build and Revert Build option for each product - below is my current code for one specific Build. I have comments explaining each line but please let me know if there is any additional information needed.

I have experience in other programming languages and am relatively new to VBA - I have no errors upon running this code, but nothing is happening in the workbook when I click a button. It should update the inventory quantities and reflect an updated inventory value (i.e. for a build, the overall inventory value should decrease since certain items have been pulled from it), but it is showing no change.


'The Name of the button in the properties window is build22A3 - when I launched the VB editor it automatically created the build22A3_Click() method name. Should these match?
Private Sub build22A3_Click()


'initialize relevant functions
Dim wkbk As Workbook
Set wkbk = ThisWorkbook



'the following for loop is intended to run through the master inventory spreadsheet PartsInventory, set as the first sheet in this workbook, and for each component that matches a component in the Bill of Materials it should deduct the BOM quantity from the inventory quantity, since those parts are now no longer available for future use


Dim row As Integer

'the for loop is to run through the entire inventory sheet. I do not know how to make this dynamic or account for adding future rows
For row = 5 To 149
Set currentCell = Worksheets("PartsInventory").Cells(row, 7)

'In the loop body, if there is data in the current cell then it should subtract that value - the Bill of Material quantity - from the overall inventory quantity
'I don't know if there is a better/more effective way to determine if a row of data on one sheet in a workbook matches a row of data on another sheet - or how to link those that match so the relevant columns in such rows on a sheet would update accordingly when they are changed on another sheet


If IsNumeric(currentCell.Value) And Len(currentCell.Value) > 0 Then
Worksheets("PartsInventory").Cells(row, 7).Formula = "=" & Worksheets("PartsInventory").Cells(row, 7) - Worksheets("22A3 BOM").Cells(row, 7)

'after the subtraction, the for loop is to iterate through the remaining rows and repeat the above process
End If
Next




End Sub
-------------------------------


Thank you again so much for your time. Any feedback is very much appreciated.

 

[AlphaFrog]

Try something like this...

[color=green]'The _Click procedure name is the same as the command button name[/color]
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] build22A3_Click()
    
    [color=green]'Declare variables[/color]
    [color=darkblue]Dim[/color] rngBOM [color=darkblue]As[/color] Range, BOMItem [color=darkblue]As[/color] Range, rngInv [color=darkblue]As[/color] Range
    
    [color=green]'Define BOM Items cell range[/color]
    [color=darkblue]With[/color] Worksheets("22A3 BOM")
        [color=darkblue]Set[/color] rngBOM = .Range("A5", .Range("A" & Rows.Count).End(xlUp))   [color=green]'BOM Items Numbers in column A from row 5 to the last used row[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] BOMItem [color=darkblue]In[/color] rngBOM [color=green]'Loop through BOM Items[/color]
        [color=green]'Find match in Inventory column A[/color]
        [color=darkblue]Set[/color] rngInv = Worksheets("PartsInventory").Columns("A").Find(What:=BOMItem.Value, _
                                                                    LookIn:=xlValues, _
                                                                    LookAt:=xlWhole, _
                                                                    SearchOrder:=xlByRows, _
                                                                    SearchDirection:=xlNext, _
                                                                    MatchCase:=False)
                                                                    
        [color=darkblue]If[/color] [color=darkblue]Not[/color] rngInv [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] [color=green]'Test if match was found[/color]
            [color=green]'Compare Quantities in column G[/color]
            [color=darkblue]If[/color] rngInv.Offset(, 6).Value >= BOMItem.Offset(, 6).Value [color=darkblue]Then[/color]                          [color=green]'Test if Inventory has sufficient quantity[/color]
                rngInv.Offset(, 6).Value = rngInv.Offset(, 6).Value - BOMItem.Offset(, 6).Value    [color=green]'Subtract BOM quantity from Inventory quantity[/color]
            [color=darkblue]Else[/color]
                MsgBox BOMItem.Value, vbExclamation, "Insufficient Inventory"                      [color=green]'Insufficient Inventory Alert[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] BOMItem
    
    MsgBox "Inventory Reduction Complete"
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]