Sub Razbivka_Kod_Seriya_Mip_Inv()
Dim w&, i&, ob&, LR&, c&, LC&
Dim B(), D(), E(), R(), S(), U(), N() As Variant
Dim Line1 As Object
Dim t As Single
Dim j As Worksheet
Dim NomerDokumenta%, KodTovara%, Naimenovanie%, RaznicaKolvo%, RaznicaGrn%, Hozoperaciya%, Napravl%
Set j = Sheets(1)
't = Timer
LR = j.Cells(Rows.Count, 1).End(xlUp).Row
LC = j.Cells(1, Columns.Count).End(xlToLeft).Column
ReDim B(1 To LR), D(1 To LR), E(1 To LR), R(1 To LR), S(1 To LR), U(1 To LR), N(1 To LR) As Variant
NomerDokumenta = j.Cells.Find("№ документа").Column
KodTovara = j.Cells.Find("Код товара").Column
Naimenovanie = j.Cells.Find("Наименование товара").Column
RaznicaKolvo = j.Cells.Find("Разница, кол-во").Column
RaznicaGrn = j.Cells.Find("Разница, грн").Column
Hozoperaciya = j.Cells.Find("Хоз.операция").Column
Napravl = j.Cells.Find("Направление").Column
For i = 2 To LR
B(i) = j.Cells(i, NomerDokumenta)
D(i) = j.Cells(i, KodTovara)
E(i) = j.Cells(i, Naimenovanie)
R(i) = j.Cells(i, RaznicaKolvo)
S(i) = j.Cells(i, RaznicaGrn)
U(i) = j.Cells(i, Hozoperaciya)
N(i) = j.Cells(i, Napravl)
Next i
For w = LBound(B) To UBound(B)
For i = LBound(B) To UBound(B)
If IsEmpty(j.Cells(w, LC + 1)) And IsEmpty(j.Cells(i, LC + 1)) Then
If D(w) = D(i) Then
If w < i Then
If (j.Cells(w, RaznicaKolvo) + j.Cells(i, RaznicaKolvo)) <> 0 Then
If R(w) < 0 And R(i) > 0 Then
If Abs(j.Cells(w, RaznicaKolvo)) > Abs(j.Cells(i, RaznicaKolvo)) Then
If N(w) Like "*МИП*" Or N(w) Like "*ИНВ*" Then
If N(i) Like "*МИП*" Or N(i) Like "*ИНВ*" Then
j.Rows(w + 1).Insert Shift:=xlDown
j.Rows(w + 1).FillDown
j.Cells(w + 1, RaznicaKolvo) = -j.Cells(i + 1, RaznicaKolvo)
j.Cells(w, RaznicaKolvo) = j.Cells(w, RaznicaKolvo) + j.Cells(i + 1, RaznicaKolvo)
j.Cells(w + 1, LC + 1) = "Разбивка/Код/Серия/МИП/ИНВ 1_" & w
j.Cells(i + 1, LC + 1) = "Разбивка/Код/Серия/МИП/ИНВ 1_" & w
LR = j.Cells(Rows.Count, 1).End(xlUp).Row
ReDim B(1 To LR), D(1 To LR), E(1 To LR), R(1 To LR), S(1 To LR), U(1 To LR), N(1 To LR) As Variant
For ob = 2 To LR
B(ob) = j.Cells(ob, NomerDokumenta)
D(ob) = j.Cells(ob, KodTovara)
E(ob) = j.Cells(ob, Naimenovanie)
R(ob) = j.Cells(ob, RaznicaKolvo)
S(ob) = j.Cells(ob, RaznicaGrn)
U(ob) = j.Cells(ob, Hozoperaciya)
N(ob) = j.Cells(ob, Napravl)
Next ob
End If
End If
ElseIf Abs(j.Cells(w, RaznicaKolvo)) < Abs(j.Cells(i, RaznicaKolvo)) Then
If N(w) Like "*МИП*" Or N(w) Like "*ИНВ*" Then
If N(i) Like "*МИП*" Or N(i) Like "*ИНВ*" Then
j.Rows(i + 1).Insert Shift:=xlDown
j.Rows(i + 1).FillDown
j.Cells(i + 1, RaznicaKolvo) = Abs(j.Cells(w, RaznicaKolvo))
j.Cells(i, RaznicaKolvo) = j.Cells(i, RaznicaKolvo) + j.Cells(w, RaznicaKolvo)
j.Cells(w, LC + 1) = "Разбивка/Код/Серия/МИП/ИНВ 2_" & w
j.Cells(i + 1, LC + 1) = "Разбивка/Код/Серия/МИП/ИНВ 2_" & w
LR = j.Cells(Rows.Count, 1).End(xlUp).Row
ReDim B(1 To LR), D(1 To LR), E(1 To LR), R(1 To LR), S(1 To LR), U(1 To LR), N(1 To LR) As Variant
For ob = 2 To LR
B(ob) = j.Cells(ob, NomerDokumenta)
D(ob) = j.Cells(ob, KodTovara)
E(ob) = j.Cells(ob, Naimenovanie)
R(ob) = j.Cells(ob, RaznicaKolvo)
S(ob) = j.Cells(ob, RaznicaGrn)
U(ob) = j.Cells(ob, Hozoperaciya)
N(ob) = j.Cells(ob, Napravl)
Next ob
End If
End If
End If
ElseIf R(w) > 0 And R(i) < 0 Then
If Abs(j.Cells(w, RaznicaKolvo)) > Abs(j.Cells(i, RaznicaKolvo)) Then
If N(w) Like "*МИП*" Or N(w) Like "*ИНВ*" Then
If N(i) Like "*МИП*" Or N(i) Like "*ИНВ*" Then
j.Rows(w + 1).Insert Shift:=xlDown
j.Rows(w + 1).FillDown
j.Cells(w + 1, RaznicaKolvo) = -j.Cells(i + 1, RaznicaKolvo)
j.Cells(w, RaznicaKolvo) = j.Cells(w, RaznicaKolvo) + j.Cells(i + 1, RaznicaKolvo)
j.Cells(w + 1, LC + 1) = "Разбивка/Код/Серия/МИП/ИНВ 3_" & w
j.Cells(i + 1, LC + 1) = "Разбивка/Код/Серия/МИП/ИНВ 3_" & w
LR = j.Cells(Rows.Count, 1).End(xlUp).Row
ReDim B(1 To LR), D(1 To LR), E(1 To LR), R(1 To LR), S(1 To LR), U(1 To LR), N(1 To LR) As Variant
For ob = 2 To LR
B(ob) = j.Cells(ob, NomerDokumenta)
D(ob) = j.Cells(ob, KodTovara)
E(ob) = j.Cells(ob, Naimenovanie)
R(ob) = j.Cells(ob, RaznicaKolvo)
S(ob) = j.Cells(ob, RaznicaGrn)
U(ob) = j.Cells(ob, Hozoperaciya)
N(ob) = j.Cells(ob, Napravl)
Next ob
End If
End If
ElseIf Abs(j.Cells(w, RaznicaKolvo)) < Abs(j.Cells(i, RaznicaKolvo)) Then
If N(w) Like "*МИП*" Or N(w) Like "*ИНВ*" Then
If N(i) Like "*МИП*" Or N(i) Like "*ИНВ*" Then
j.Rows(i + 1).Insert Shift:=xlDown
j.Rows(i + 1).FillDown
j.Cells(i + 1, RaznicaKolvo) = -j.Cells(w, RaznicaKolvo)
j.Cells(i, RaznicaKolvo) = j.Cells(i, RaznicaKolvo) + j.Cells(w, RaznicaKolvo)
j.Cells(w, LC + 1) = "Разбивка/Код/Серия/МИП/ИНВ 4_" & w
j.Cells(i + 1, LC + 1) = "Разбивка/Код/Серия/МИП/ИНВ 4_" & w
LR = j.Cells(Rows.Count, 1).End(xlUp).Row
ReDim B(1 To LR), D(1 To LR), E(1 To LR), R(1 To LR), S(1 To LR), U(1 To LR), N(1 To LR) As Variant
For ob = 2 To LR
B(ob) = j.Cells(ob, NomerDokumenta)
D(ob) = j.Cells(ob, KodTovara)
E(ob) = j.Cells(ob, Naimenovanie)
R(ob) = j.Cells(ob, RaznicaKolvo)
S(ob) = j.Cells(ob, RaznicaGrn)
U(ob) = j.Cells(ob, Hozoperaciya)
N(ob) = j.Cells(ob, Napravl)
Next ob
End If
End If
End If
End If
End If
End If
End If
End If
Next i
LR = j.Cells(Rows.Count, 1).End(xlUp).Row
ReDim Preserve B(1 To LR), D(1 To LR), E(1 To LR), R(1 To LR), S(1 To LR), U(1 To LR), N(1 To LR) As Variant
For ob = 2 To LR
B(ob) = j.Cells(ob, NomerDokumenta)
D(ob) = j.Cells(ob, KodTovara)
E(ob) = j.Cells(ob, Naimenovanie)
R(ob) = j.Cells(ob, RaznicaKolvo)
S(ob) = j.Cells(ob, RaznicaGrn)
U(ob) = j.Cells(ob, Hozoperaciya)
N(ob) = j.Cells(ob, Napravl)
Next ob
Debug.Print w
Next w
'MsgBox (Timer - t) / 60
End Sub
|
