Else
If MemoOrder(Index; SumS; 6; 8; Pos812; StrS) Then Exit Sub
End If
End If
Index = Index + 1
End If
If CliNum <> FilialConst Then
If Len(StrComS) > 0 Then
StrComS = StrComS + "," + CStr(Right(CliNum; 3))
Else
StrComS = StrComS + CStr(Right(CliNum; 3))
End If
End If
If CliNum <> FilialConst Then ComSum = ComSum + Page.Cells(16; 6)
Worksheets("ОтчетыИнвесторам").Select
'---------------
Rows(CStr(m + 4) + ":" + CStr(m + 4)).RowHeight = 13,8
Rows(CStr(m + 4) + ":" + CStr(m + 4)).WrapText = False
Rows(CStr(m + 4) + ":" + CStr(m + 4)).HorizontalAlignment = xlRight
Rows(CStr(m + 4) + ":" + CStr(m + 4)).VerticalAlignment = xlBottom
Range(Cells(NN; 2); Cells(NN + 200; 6)).Delete shift:=xlToLeft
m = NN
FlagBuy = True
FlagCell = True
ComBirga = 0
sum = 0
SumBuy = 0
SumCom = 0
End If
End If
i = i + 1
Loop
If Not FlagDeal Then
MsgBox "Сделок в текущий день не было"
Else
If ComSum > 0 Then
Worksheets("Ордер").Select
If MemoOrder(Index; ComSum; 9; 7; 2; _
"Комиссия ВКБ по инвесторам " + StrComS + " в т.ч. НДС " + _
CStr(Format(ComSum / 6; "0,00"))) Then Exit Sub
End If
End If
End Sub
'-------------------------------- Печать Отчеты недельные ----------
Sub PrintOtchWeek()
Dim BumNum; CliNum; i; j; k; a; n; Sign; s As Integer
Dim Flag As Boolean
Dim Code As Long
Dim Str As String
Dim DepoFil() As Integer
Dim Num As Integer
CurDate = Worksheets("Врем").Cells(1; 4)
Call FormBum
Sheets("ОтчетНедельный").Select
BumNum = Worksheets("Врем").Cells(1; 2)
Num = 8
For i = 1 To BumNum
Cells(6; i + 1) = Worksheets("Врем").Cells(i; 1)
Cells(6; i + 1).Font.Bold = True
Cells(6; i + 1).Interior.ColorIndex = 40
Cells(Num; i + 1).Interior.ColorIndex = 15
Cells(Num; i + 1) = ""
Cells(5; i + 1).Interior.ColorIndex = 40
Next
Cells(Num; 1).Interior.ColorIndex = 15
Cells(Num; 1) = ""
Cells(5; 1).Interior.ColorIndex = 40
Cells(5; 1) = ""
Cells(6; 1).Interior.ColorIndex = 40
Cells(6; 1).Font.Bold = True
Cells(6; 1) = "№ бумаги"
Cells(7; 1) = "Дилер"
Cells(6; 1).HorizontalAlignment = xlCenter
Cells(7; 1).HorizontalAlignment = xlCenter
Cells(7; 1).Font.Bold = True
CliNum = Worksheets("Врем").Cells(1; 3)
ReDim DepoArray(CliNum; BumNum)
a = 2
While Worksheets("Сделки").Cells(a; 1) <> Empty
i = 1
While Worksheets("Клиенты").Cells(i + 1; 2) <> _
Worksheets("Сделки").Cells(a; 2)
If Worksheets("Клиенты").Cells(i + 1; 2) = Empty Then
MsgBox "Неверный номер клиента в Окне 'Сделки'"
Exit Sub
End If
i = i + 1
Wend
k = 0
For j = 1 To BumNum
If Worksheets("Врем").Cells(j; 1) = Worksheets("Сделки").Cells(a; 3) Then
k = j
Exit For
End If
Next
If k = 0 Then
a = a + 1
GoTo NNN
End If
If Not IsEmpty(Worksheets("Сделки").Cells(a; 4)) Then
Sign = 1
Else
Sign = -1
End If
If CurDate >= Worksheets("Сделки").Cells(a; 1) Then
DepoArray(i; k) = DepoArray(i; k) + Sign * Worksheets("Сделки").Cells(a; 6)
End If
a = a + 1
NNN:
Wend
For k = 1 To BumNum
DepoArray(1; k) = DepoArray(1; k) + DepoArray(2; k)
DepoArray(2; k) = 0
Next k
n = 7
For i = 1 To CliNum
Flag = False
For k = 1 To BumNum
If DepoArray(i; k) > 0 Then Flag = True
Next
If Flag Then
If n > 7 Then
Str = Format(Worksheets("Клиенты").Cells(i + 1; 2); "0000000000")
Str = Right(Str; 5)
Cells(n; 1).NumberFormat = "@"
Cells(n; 1).Font.Bold = True
Cells(n; 1).HorizontalAlignment = xlCenter
Cells(n; 1).Font.Italic = False
Cells(n; 1).Interior.ColorIndex = 2
Cells(n; 1) = Str
End If
For k = 1 To BumNum
If DepoArray(i; k) <> 0 Then
Cells(n; k + 1) = DepoArray(i; k)
Else
Cells(n; k + 1) = ""
End If
Cells(n; k + 1).Font.Bold = False
Cells(n; k + 1).Font.Italic = False
Cells(n; k + 1).Interior.ColorIndex = 2
Next
If n = 7 Then
n = n + 2
Else
n = n + 1
End If
End If
Next
For i = 1 To BumNum
Cells(n; i + 1).Interior.ColorIndex = 40
s = 0
For k = 9 To n - 1
s = s + Cells(k; i + 1)
Next
Cells(n; i + 1).Value = s
Next
Cells(n; 1).Interior.ColorIndex = 40
Cells(n; 1) = "Итого по инвесторам"
Cells(n; 1).Font.Bold = True
Cells(n; 1).Font.Italic = True
Range("A1:Z200").Borders(xlLeft).LineStyle = xlNone
Range("A1:Z200").Borders(xlRight).LineStyle = xlNone
Range("A1:Z200").Borders(xlTop).LineStyle = xlNone
Range("A1:Z200").Borders(xlBottom).LineStyle = xlNone
Range("A1:Z200").BorderAround LineStyle:=xlNone
Range(Cells(5; 1); Cells(n; BumNum + 1)).Borders(xlLeft).Weight = xlThin
Range(Cells(5; 1); Cells(n; BumNum + 1)).Borders(xlRight).Weight = xlThin
Range(Cells(5; 1); Cells(n; BumNum + 1)).Borders(xlTop).Weight = xlThin
Range(Cells(5; 1); Cells(n; BumNum + 1)).Borders(xlBottom).Weight = xlThin
Range(Cells(5; 1); Cells(n; BumNum + 1)).BorderAround Weight:=xlMedium
Range(Cells(n + 1; 1); Cells(100; 30)).Delete shift:=xlToLeft
Range(Cells(1; BumNum + 2); Cells(100; 30)).Delete shift:=xlToLeft
Range("a2") = "на " + CStr(CurDate)
Range(Cells(n + 2; 1); Cells(n + 3; BumNum + 1)).BorderAround Weight:=xlMedium
Cells(n + 2; 1) = "Количество перечисленных облигаций на счета ""Депо"""
Cells(n + 3; 1) = "без совершения сделок купли-продажи"
Cells(n + 2; 1).Font.Bold = True
Cells(n + 3; 1).Font.Bold = True
Cells(n + 5; 1).Font.Size = 12
Cells(n + 5; 1) = "Ответственное лицо Дилера " + _
" _________________________ "
Cells(n + 3; BumNum + 1) = 0
Cells(n + 3; BumNum + 1).Font.Bold = True
If DialogPrint("ОтчетНедельный"; 2) Then Exit Sub
End Sub
'-------------------------------- Печать Отчеты Месячные -----------
Sub PrintOtchMonth()
Dim DateBegin; DateEnd; DateMas() As Date
Dim i; k; m; NumberClients; kk As Long
Dim Sign; BumNum; Row; Col; Num; sum As Integer
Dim DateFlag; Flag; CliInput(); BumInput() As Boolean
Dim Bum(ConstMaxBum) As Long
Dim mas() As Integer
Dim Sheet As Object
Dim Str As String
With DialogSheets("ДиалогМесОтчет")
.EditBoxes(1).InputType = xlDate
.EditBoxes(2).InputType = xlDate
.Show
If Not Button Then Exit Sub
If IsDate(.EditBoxes(1).Text) = False Or _
IsDate(.EditBoxes(2).Text) = False Then
MsgBox "Неверно введены даты"
Exit Sub
End If
DateBegin = CDate(.EditBoxes(1).Text)
DateEnd = CDate(.EditBoxes(2).Text)
If DateBegin >= DateEnd Then
MsgBox "Даты не пересекаются"
Exit Sub
End If
End With
Set Sheet = Worksheets("Бумаги")
i = 2
BumNum = 0
While Sheet.Cells(i; 1) <> Empty
If (Sheet.Cells(i; 2) < DateBegin And Sheet.Cells(i; 3) > DateBegin) Or _
(Sheet.Cells(i; 2) < DateEnd And Sheet.Cells(i; 3) > DateEnd) Or _
(Sheet.Cells(i; 2) > DateBegin And Sheet.Cells(i; 3) < DateEnd) Then
Bum(BumNum + 1) = Sheet.Cells(i; 1)
BumNum = BumNum + 1
End If
i = i + 1
Wend
Set Sheet = Worksheets("Клиенты")
i = 2
k = 0
While Sheet.Cells(i; 1) <> Empty
If Sheet.Cells(i; 2) > k And Sheet.Cells(i; 2) <> FilialConst Then
k = Sheet.Cells(i; 2)
End If
i = i + 1
Wend
NumberClients = k - DilerConst
DateFlag = True
ReDim mas(NumberClients; BumNum * 7)
ReDim DateMas(NumberClients; BumNum)
ReDim CliInput(NumberClients)
ReDim BumInput(BumNum)
i = 2
Worksheets("Сделки").Select
While Cells(i; 1) <> Empty
If Cells(i; 2) <> DilerConst And Cells(i; 2) <> FilialConst Then
If Cells(i; 1) < DateBegin Then
Flag = True
For k = 1 To BumNum ' поиск номера бумаги
If Cells(i; 3) = Bum(k) Then
Flag = False
Exit For
End If
Next k
If Flag Then GoTo cont
Sign = 1
If IsEmpty(Cells(i; 4)) Then Sign = -1
mas(Cells(i; 2) - DilerConst; (k - 1) * 7 + 1) = _
mas(Cells(i; 2) - DilerConst; (k - 1) * 7 + 1) + Sign * Cells(i; 6)
End If
If Cells(i; 1) >= DateBegin And DateFlag Then
For k = 1 To NumberClients
For m = 1 To BumNum
mas(k; (m - 1) * 7 + 2) = mas(k; (m - 1) * 7 + 1)
Next m
Next k
DateFlag = False
End If
If Cells(i; 1) >= DateBegin And Cells(i; 1) <= DateEnd Then
Flag = True
For k = 1 To BumNum
If Cells(i; 3) = Bum(k) Then
Flag = False
Exit For
End If
Next k
If Flag Then GoTo cont
If Cells(i; 7) <> "списание" And Cells(i; 7) <> "зачисление" Then
If Not IsEmpty(Cells(i; 4)) Then
mas(Cells(i; 2) - DilerConst; (k - 1) * 7 + 3) = _
mas(Cells(i; 2) - DilerConst; (k - 1) * 7 + 3) + Cells(i; 6)
Else
mas(Cells(i; 2) - DilerConst; (k - 1) * 7 + 4) = _
mas(Cells(i; 2) - DilerConst; (k - 1) * 7 + 4) + Cells(i; 6)
End If
If DateMas(Cells(i; 2) - DilerConst; k) <> Cells(i; 1) Then
DateMas(Cells(i; 2) - DilerConst; k) = Cells(i; 1)
mas(Cells(i; 2) - DilerConst; (k - 1) * 7 + 5) = _
mas(Cells(i; 2) - DilerConst; (k - 1) * 7 + 5) + 1
End If
End If
If Cells(i; 7) = "списание" Then
mas(Cells(i; 2) - DilerConst; (k - 1) * 7 + 6) = _
mas(Cells(i; 2) - DilerConst; (k - 1) * 7 + 6) + Cells(i; 6)
End If
If Cells(i; 7) = "зачисление" Then
mas(Cells(i; 2) - DilerConst; (k - 1) * 7 + 7) = _
mas(Cells(i; 2) - DilerConst; (k - 1) * 7 + 7) + Cells(i; 6)
End If
Sign = 1
If IsEmpty(Cells(i; 4)) Then Sign = -1
mas(Cells(i; 2) - DilerConst; (k - 1) * 7 + 2) = _
mas(Cells(i; 2) - DilerConst; (k - 1) * 7 + 2) + Sign * Cells(i; 6)
End If
End If
cont:
i = i + 1
Wend
For i = 1 To NumberClients
CliInput(i) = False
For k = 1 To BumNum
If mas(i; (k - 1) * 7 + 1) > 0 Or _
mas(i; (k - 1) * 7 + 2) > 0 Or _
mas(i; (k - 1) * 7 + 3) > 0 Or _
mas(i; (k - 1) * 7 + 4) > 0 Or _
mas(i; (k - 1) * 7 + 5) > 0 Or _
mas(i; (k - 1) * 7 + 6) > 0 Or _
mas(i; (k - 1) * 7 + 7) > 0 Then CliInput(i) = True
Next k
Next i
For k = 1 To BumNum
BumInput(k) = False
For i = 1 To NumberClients
If mas(i; (k - 1) * 7 + 1) > 0 Or _
mas(i; (k - 1) * 7 + 2) > 0 Or _
mas(i; (k - 1) * 7 + 3) > 0 Or _
mas(i; (k - 1) * 7 + 4) > 0 Or _
mas(i; (k - 1) * 7 + 5) > 0 Or _
mas(i; (k - 1) * 7 + 6) > 0 Or _
mas(i; (k - 1) * 7 + 7) > 0 Then BumInput(k) = True
Next i
Next k
Worksheets("ОтчетМесячный").Select
Range(Cells(7; 1); Cells(800; 22)).Delete shift:=xlToLeft
Row = 4
Col = 2
Cells(2; 1) = "за период от " + CStr(DateBegin) + " до " + CStr(DateEnd)
kk = 0
Flag = False
For k = 1 To BumNum
If BumInput(k) Then
Cells(Row; Col) = Bum(k)
Num = 0
For i = 1 To NumberClients
If CliInput(i) Then
If Col = 2 Then
Str = Format(i; "0000000000")
Str = Right(Str; 5)
Cells(Row + Num + 3; Col - 1).NumberFormat = "@"
Cells(Row + Num + 3; Col - 1).Font.Bold = True
Cells(Row + Num + 3; Col - 1).HorizontalAlignment = xlCenter
Cells(Row + Num + 3; Col - 1).Font.Italic = False
Cells(Row + Num + 3; Col - 1).Interior.ColorIndex = 2
Cells(Row + Num + 3; Col - 1) = Str
End If
Cells(Row + Num + 3; Col) = mas(i; (k - 1) * 7 + 1)
Cells(Row + Num + 3; Col + 1) = mas(i; (k - 1) * 7 + 2)
Cells(Row + Num + 3; Col + 2) = mas(i; (k - 1) * 7 + 3)
Cells(Row + Num + 3; Col + 3) = mas(i; (k - 1) * 7 + 4)
Cells(Row + Num + 3; Col + 4) = mas(i; (k - 1) * 7 + 5)
Cells(Row + Num + 3; Col + 5) = mas(i; (k - 1) * 7 + 6)
Cells(Row + Num + 3; Col + 6) = mas(i; (k - 1) * 7 + 7)
Num = Num + 1
End If
Next i
Col = Col + 7
kk = kk + 1
Flag = True
End If
If ((kk > 0) And (kk Mod 3 = 0) And Flag) Or k = BumNum Then
Flag = False
For i = 2 To 22
sum = 0
For m = 1 To NumberClients
sum = sum + Cells(m + 6; i)
Next m
Cells(Num + 7; i) = sum
Cells(Num + 7; i).Font.Bold = True
Cells(Num + 7; i).Interior.ColorIndex = 15
Next i
Cells(Num + 7; 1) = "Итого"
Cells(Num + 7; 1).Font.Bold = True
Cells(Num + 7; 1).HorizontalAlignment = xlCenter
Cells(Num + 7; 1).Interior.ColorIndex = 15
Range(Cells(7; 1); Cells(Num + 7; 22)).Borders(xlLeft).Weight = xlThin
Range(Cells(7; 1); Cells(Num + 7; 22)).Borders(xlRight).Weight = xlThin
Range(Cells(7; 1); Cells(Num + 7; 22)).Borders(xlTop).Weight = xlThin
Range(Cells(7; 1); Cells(Num + 7; 22)).Borders(xlBottom).Weight = xlThin
Range(Cells(7; 1); Cells(Num + 7; 22)).BorderAround Weight:=xlMedium
Range(Cells(7; 9); Cells(Num + 7; 15)).BorderAround Weight:=xlMedium
Cells(Num + 10; 10) = "Ответственное лицо Дилера______________________________"
If DialogPrint("ОтчетМесячный"; 2) Then Exit Sub
Row = 4
Col = 2
Cells(Row; Col) = " "
Cells(Row; Col + 7) = " "
Cells(Row; Col + 14) = " "
Range(Cells(7; 1); Cells(800; 22)).Delete shift:=xlToLeft
End If
Next k
Worksheets("СписокКлиентов").Select
Num = 5
Range(Cells(Num; 1); Cells(100; 3)).Delete shift:=xlToLeft
For i = 1 To NumberClients
If CliInput(i) Then
k = 2
While Sheet.Cells(k; 2) <> DilerConst + i
k = k + 1
Wend
Cells(Num; 1) = Sheet.Cells(k; 1)
Cells(Num; 2) = Sheet.Cells(k; 2)
Cells(Num; 3) = Sheet.Cells(k; 3)
Cells(Num; 1).HorizontalAlignment = xlLeft
Cells(Num; 2).HorizontalAlignment = xlCenter
Cells(Num; 3).HorizontalAlignment = xlCenter
Cells(Num; 3).WrapText = True
Num = Num + 1
End If
Next i
Cells(2; 1) = "за период от " + CStr(DateBegin) + " до " + CStr(DateEnd)
Range(Cells(5; 1); Cells(Num - 1; 3)).Borders(xlLeft).Weight = xlThin
Range(Cells(5; 1); Cells(Num - 1; 3)).Borders(xlRight).Weight = xlThin
Range(Cells(5; 1); Cells(Num - 1; 3)).Borders(xlTop).Weight = xlThin
Range(Cells(5; 1); Cells(Num - 1; 3)).Borders(xlBottom).Weight = xlThin
Range(Cells(5; 1); Cells(Num - 1; 3)).BorderAround Weight:=xlMedium
Range(Cells(5; 2); Cells(Num - 1; 2)).BorderAround Weight:=xlMedium
Cells(Num + 2; 2) = "Ответственное лицо Дилера______________________________"
With DialogSheets("ДиалогПечать")
AgainMonthOtch1:
Просмотр = False
ExitVar = False
Button = False
.Show
If Просмотр Then
Worksheets("СписокКлиентов").PrintPreview
GoTo AgainMonthOtch1