|
Sub 生成评估表()
Application.ScreenUpdating = False
Dim ar As Variant
Dim rn As Range
Dim i As Long, r As Long, rs As Long
Dim br()
With Sheets("明细表")
r = .Cells(Rows.Count, 1).End(xlUp).Row
If r < 4 Then MsgBox "明细表为空!": End
ar = .Range(.Cells(3, 1), .Cells(r, 28))
zd = Format(Application.Max(.Range("r4:r" & r)), "yyyy/m/d")
End With
ReDim br(1 To UBound(ar), 1 To 6)
For i = 2 To UBound(ar)
If ar(i, 18) <> "" Then
If IsDate(ar(i, 18)) Then
If ar(i, 18) = CDate(zd) Then
n = n + 1
If ar(i, 24) <> "" And ar(i, 26) <> "" Then
xz = "卖出"
br(n, 1) = "二级卖出"
Else
br(n, 1) = "二级买入"
xz = "买入"
End If
br(n, 2) = ar(i, 28)
br(n, 3) = ar(i, 2)
br(n, 4) = ar(i, 4)
br(n, 5) = ar(i, 15)
br(n, 6) = ar(i, 27)
If mc = "" Then
mc = ar(i, 4) & "债券与上海国际、上海国利、省内农商行等询价" & xz & ",交易对手为 "
Else
mc = mc & Chr(10) & ar(i, 4) & "债券与上海国际、上海国利、省内农商行等询价" & xz & ",交易对手为 "
End If
End If
End If
End If
Next i
With Sheets("评估表")
Set rn = .Columns(1).Find("项目简介", , , , , , 1)
If rn Is Nothing Then MsgBox "评估表中缺少 项目简介 标识字段": End
js = rn.Row
.Cells(js, 2) = mc
If js >= 4 Then .Range("b4:k" & js - 1) = Empty
hs = js - 4
If hs < n Then
ys = n - hs
.Rows(js - 1 & ":" & js + ys - 2).Insert Shift:=xlDown
ElseIf hs > n Then
.Rows(n + 4 & ":" & js - 1).Delete
End If
.[b4].Resize(n, UBound(br, 2)) = br
.[b4].Resize(n, UBound(br, 2)).Borders.LineStyle = 1
End With
Application.ScreenUpdating = True
MsgBox "ok!"
End Sub
|
|