改其中一部分,其他照着写吧。要学会用数组处理问题。- Sub 盘方查询()
- Application.ScreenUpdating = False
- Dim x, Sh As Worksheet, pf$, czf$, arr, brr
- x = 3
- With Sheets("录入登记表")
- brr = .[a1:m7] '当前显示区域
- pf = brr(3, 3): czf = brr(3, 7) '盘方、出资方
- If pf = "" And czf = "" Then
- .Cells(1, 10) = "请在操盘方姓名或出资方姓名选择要查询的客户"
- .Cells(1, 9) = "温馨→提示→"
- Exit Sub
- End If
-
- arr = Sheets("盘资方基本表").[a1].CurrentRegion '数据源
- For x = 1 To UBound(arr)
- If arr(x, 2) = pf And arr(x, 13) = czf Then '当当前区域的盘方、出资方和数据源的盘方、出资方相等
- brr(3, 3) = arr(x, 2) '操盘方姓名
- brr(3, 5) = arr(x, 3) '客户状态
- brr(4, 3) = arr(x, 4) '联系方式
- brr(4, 5) = arr(x, 5) '客户经理
- brr(5, 3) = arr(x, 6) '应收月息利率
- brr(5, 5) = arr(x, 7) '固定收息日期
- brr(6, 3) = arr(x, 8) '本月应收利息
- brr(6, 5) = arr(x, 9) '截止今日应收利息
- brr(7, 3) = arr(x, 10) '保证金初始金额
- brr(7, 5) = arr(x, 11) '保证金当前余额
- brr(3, 7) = arr(x, 13) '出资方姓名
- brr(3, 9) = arr(x, 14) '客户状态
- brr(4, 7) = arr(x, 15) '联系方式
- brr(4, 9) = arr(x, 16) '客户经理
- brr(5, 7) = arr(x, 17) '支付月息利率
- brr(5, 9) = arr(x, 18) '固定付息日期
- brr(6, 7) = arr(x, 19) '本月应付利息
- brr(6, 9) = arr(x, 20) '截止今日应付利息
- brr(7, 7) = arr(x, 21) '初始出资金额
- brr(7, 9) = arr(x, 22) '当前出资余额
- brr(3, 11) = arr(x, 23) '证券公司账号
- brr(4, 11) = arr(x, 24) '三方存管银行
- brr(5, 11) = arr(x, 25) '利息支付银行
- brr(1, 10) = "读取数据成功!"
- Exit For
- End If
- Next
- .[a1:m7] = brr '重写当前显示区域
- End With
- Application.ScreenUpdating = True
- End Sub
复制代码 |