Sub jjj_actualize_stocs()
Dim arrTmp, i As Long, wb As Workbook, wsh As Worksheet, lLastRow As Long, _
rngShop As Range, arrShop, dictShop As Object, _
rngStorage As Range, arrStorage, dictStorage As Object
Const sSTORAGE_NAME_WSH As String = "склад"
Const sSHOP_NAME_WSH As String = "магазин"
Set wb = ActiveWorkbook
' - проверили наличие листов склад, магазин
arrTmp = Array(sSTORAGE_NAME_WSH, sSHOP_NAME_WSH)
With wb
On Error Resume Next
For i = LBound(arrTmp, 1) To UBound(arrTmp, 1)
Set wsh = .Sheets(arrTmp(i))
If wsh Is Nothing Then
MsgBox "Ключевой лист """ & arrTmp(i) & """" & Chr(10) & _
"отсутствует в книге """ & .Name & """." & Chr(10) & _
"Макрос прерывает свою работу.", vbCritical
Exit Sub
End If
Next i
On Error GoTo 0
' - считать продажи маг. в массив (ш/к, продано)
With .Sheets(sSHOP_NAME_WSH)
lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
If .Range("A2").Row > lLastRow Then
MsgBox "Продаж в магазине не обнаружено." & Chr(10) & _
"Макрос прерывает свою работу.", vbCritical
Exit Sub
End If
Set rngShop = .Range("A2:D" & lLastRow)
End With ' .Sheets(sSHOP_NAME_WSH)
Set dictShop = CreateObject("scripting.dictionary")
arrShop = rngShop.Value
' - собрали словарь с массива магазина (ключ: ш/к; значение: кол-во продаж)
For i = 1 To UBound(arrShop, 1)
If dictShop.exists(arrShop(i, 1)) Then
dictShop(arrShop(i, 1)) = dictShop(arrShop(i, 1)) + arrShop(i, 4)
Else
dictShop(arrShop(i, 1)) = arrShop(i, 4)
End If
arrShop(i, 4) = Empty
Next i
' - считать продажи склада в массив (ш/к, кол-во)
With .Sheets(sSTORAGE_NAME_WSH)
lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
If .Range("A5").Row > lLastRow Then
MsgBox "Остатков на складе не обнаружено." & Chr(10) & _
"Макрос прерывает свою работу.", vbCritical
Exit Sub
End If
Set rngStorage = .Range("A5:C" & lLastRow)
End With ' .Sheets(sSTORAGE_NAME_WSH)
Set dictStorage = CreateObject("scripting.dictionary")
arrStorage = rngStorage.Value
' - собрали словарь со склада (ключ: ш/к; значение: любое)
For i = 1 To UBound(arrStorage, 1)
' - если есть дубли по ш/к на складе - прервать макрос
If dictStorage.exists(arrStorage(i, 1)) Then
MsgBox "На складе обнаружено дублирование по ш/к """ & arrStorage(i, 1) & """." & Chr(10) & _
"Макрос прерывает свою работу.", vbCritical
Exit Sub
Else
dictStorage(arrStorage(i, 1)) = arrStorage(i, 3)
End If
If dictShop.exists(arrStorage(i, 1)) Then
' - от остатка отняли продажи
arrStorage(i, 3) = arrStorage(i, 3) - dictShop(arrStorage(i, 1))
' - отнятые продажи удалили из словаря маг.
dictShop.Remove (arrStorage(i, 1))
End If
Next i
End With ' wb
' - массив маг. на соотв. лист
rngShop.Value = arrShop
' - массив склада на соотв. лист
rngStorage.Value = arrStorage
' - если после цикла в словаре маг. что-то осталось, то сообщить об этом
If dictShop.Count > 0 Then
MsgBox "Магазином были проданы товары, которых нет на складе!", vbCritical
Else
MsgBox "Work complete!", vbInformation
End If
End Sub
|