Смекни!
smekni.com

Анализ эффективности вложений денежных средств в РКО (стр. 6 из 10)

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