进销存管理核心代码

入库时自动更新库存

Sub StockIn()
    Dim productID As String
    Dim qty As Integer
    Dim findRow As Range
    productID = Sheets("入库记录").Cells(2, 2).Value
    qty = Sheets("入库记录").Cells(2, 5).Value
    Set findRow = Sheets("商品管理").Columns(2).Find(productID, LookAt:=xlWhole)
    If Not findRow Is Nothing Then
        findRow.Offset(0, 9).Value = findRow.Offset(0, 9).Value + qty
        MsgBox "入库成功！当前库存: " & findRow.Offset(0, 9).Value
    Else
        MsgBox "未找到该商品编号！"
    End If
End Sub

库存预警检查

Sub CheckStockAlert()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long, alertCount As Integer
    Set ws = ThisWorkbook.Sheets("商品管理")
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        If ws.Cells(i, "J").Value <= ws.Cells(i, "I").Value Then
            ws.Cells(i, "J").Interior.Color = RGB(255, 200, 200)
            alertCount = alertCount + 1
        End If
    Next
    MsgBox alertCount & " 种商品库存低于预警线！"
End Sub