(VBA) I am looking to automate the update of my inventory form when I activate a Macro to add the co-CodePudding

I am working on an Inventory workbook that has the inventory in one sheet and another sheet where I can scan barcode into and activate a macro to update the inventory sheet.

The goal is to read each cell in a column that has a value and then find the matching value in the other sheet and update the count by using the sum of the corresponding count values.

The VBA code I have so far updates the first item on the list, but just continues to update it endlessly. I am not sure what the best approach is and looking for a better route to update it in an efficient manner.

This is what I have so far

`Sub Inventory_Update()

Dim i As Integer
Dim b As Integer

i = 2
Do While Cells(i, "D").Value <> ""
 If Cells(i, "D").Value <> "" Then
  b = 1
  Do While b < 346
  If Sheet1.Cells(b, "B").Value = Cells(i, "D").Value Then
  Sheet1.Cells(b, "C").Value = Sheet1.Cells(b, "C").Value   Cells(i, "F").Value
  Else
  b = b   1
  Loop
  i = i   1
  End If
  Loop

End Sub

CodePudding user response：

Like this - a For Next loop is better for b

Sub Inventory_Update()
    Dim i As Long, b As Long, wsInv As Worksheet, wsEntry As Worksheet
    
    Set wsEntry = ThisWorkbook.Worksheets("Entry") 'for example
    Set wsInv = ThisWorkbook.Worksheets("Inventory") 'or Sheet1
    
    i = 2
    'use a worksheet reference whenver you reference a range
    Do While Len(wsEntry.Cells(i, "D").Value) > 0
        For b = 1 To 346
            If wsInv.Cells(b, "B") = wsEntry.Cells(i, "D").Value Then
                With wsInv.Cells(b, "C")
                    .Value = .Value   wsEntry.Cells(i, "F").Value
                End With
            End If
        Next b
        i = i   1
    Loop
End Sub