代码 实现功能,根据第一张sheet中客户编号数据从另一个sheet中匹配到客户详细信息后,把客户信息提取复制到第一个sheet中客户编号后,这个过程速度非常慢,不知道哪里有问题
Private Sub cbAddCIS_Click() Dim pvtTable As PivotTable
Dim oListObj As ListObject, oLO As ListObject
Dim f_rng As Range
Dim oNewRow As ListRow
Dim f_rowno As Long, f_colno As Long, t_colno As Long, r_pos As Long,
c_pos As Long
Dim lookup_value As String, lookup_col_name As String, result_col_name As
String
Dim found As Boolean On Error Resume Next Set pvtTable = ActiveSheet.PivotTables("客户资产汇总表")
Set f_rng = pvtTable.RowRange
Set oListObj = Worksheets("客户资产汇总").ListObjects("附加客户信息")
Set oLO = Worksheets("客户信息").ListObjects("客户信息表") Call cbDelCIS_Click '先清除附加的客户信息 For f_rowno = 2 To f_rng.Rows.Count - 1 '处理每个行数据(去除标题行和汇总
行)
'插入一空行
Set oNewRow = oListObj.ListRows.Add '用非空的查找要素(客户号、身份证或姓名)去查找客户信息
found = False
For f_colno = 1 To f_rng.Columns.Count lookup_value = f_rng.Cells(f_rowno, f_colno)
lookup_col_name = f_rng.Cells(1, f_colno) If (lookup_value <> "" And lookup_value <> "(空白)") Then r_pos = 0
r_pos = WorksheetFunction.Match(lookup_value, oLO.ListColumns
(lookup_col_name).DataBodyRange, 0)
If r_pos > 0 Then
found = True
Exit For
End If End If Next If found Then '找到的话,逐一添加客户信息各要素
For t_colno = 1 To oListObj.ListColumns.Count
result_col_name = oListObj.ListColumns(t_colno).Name c_pos = 0
c_pos = WorksheetFunction.Match(result_col_name,
oLO.HeaderRowRange, 0)
If c_pos > 0 Then
oNewRow.Range.Cells(1, t_colno) = oLO.DataBodyRange.Cells(r_pos,
c_pos)
End If Next
End If NextEnd SubPrivate Sub cbDelCIS_Click()
Dim rng As Range Set rng = Worksheets("客户资产汇总").ListObjects("附加客户信息
").DataBodyRange If rng Is Nothing Then
Exit Sub
End If rng.DeleteEnd SubPrivate Sub cbrefresh_Click() Dim pvtTable As PivotTable Set pvtTable = ActiveSheet.PivotTables("客户资产汇总表") pvtTable.RefreshTable If Worksheets("客户资产汇总").ListObjects("附加客户信息").DataBodyRange
Is Nothing Then
Exit Sub
End If 'Call cbAddCIS_ClickEnd Sub
Private Sub cbAddCIS_Click() Dim pvtTable As PivotTable
Dim oListObj As ListObject, oLO As ListObject
Dim f_rng As Range
Dim oNewRow As ListRow
Dim f_rowno As Long, f_colno As Long, t_colno As Long, r_pos As Long,
c_pos As Long
Dim lookup_value As String, lookup_col_name As String, result_col_name As
String
Dim found As Boolean On Error Resume Next Set pvtTable = ActiveSheet.PivotTables("客户资产汇总表")
Set f_rng = pvtTable.RowRange
Set oListObj = Worksheets("客户资产汇总").ListObjects("附加客户信息")
Set oLO = Worksheets("客户信息").ListObjects("客户信息表") Call cbDelCIS_Click '先清除附加的客户信息 For f_rowno = 2 To f_rng.Rows.Count - 1 '处理每个行数据(去除标题行和汇总
行)
'插入一空行
Set oNewRow = oListObj.ListRows.Add '用非空的查找要素(客户号、身份证或姓名)去查找客户信息
found = False
For f_colno = 1 To f_rng.Columns.Count lookup_value = f_rng.Cells(f_rowno, f_colno)
lookup_col_name = f_rng.Cells(1, f_colno) If (lookup_value <> "" And lookup_value <> "(空白)") Then r_pos = 0
r_pos = WorksheetFunction.Match(lookup_value, oLO.ListColumns
(lookup_col_name).DataBodyRange, 0)
If r_pos > 0 Then
found = True
Exit For
End If End If Next If found Then '找到的话,逐一添加客户信息各要素
For t_colno = 1 To oListObj.ListColumns.Count
result_col_name = oListObj.ListColumns(t_colno).Name c_pos = 0
c_pos = WorksheetFunction.Match(result_col_name,
oLO.HeaderRowRange, 0)
If c_pos > 0 Then
oNewRow.Range.Cells(1, t_colno) = oLO.DataBodyRange.Cells(r_pos,
c_pos)
End If Next
End If NextEnd SubPrivate Sub cbDelCIS_Click()
Dim rng As Range Set rng = Worksheets("客户资产汇总").ListObjects("附加客户信息
").DataBodyRange If rng Is Nothing Then
Exit Sub
End If rng.DeleteEnd SubPrivate Sub cbrefresh_Click() Dim pvtTable As PivotTable Set pvtTable = ActiveSheet.PivotTables("客户资产汇总表") pvtTable.RefreshTable If Worksheets("客户资产汇总").ListObjects("附加客户信息").DataBodyRange
Is Nothing Then
Exit Sub
End If 'Call cbAddCIS_ClickEnd Sub
请参阅 应用于 示例 特性
如果屏幕更新功能是打开的,则该值为 True。Boolean 类型,可读写。说明
关闭屏幕更新可加快宏的执行速度。这样将看不到宏的执行过程,但宏的执行速度加快了。当宏结束运行后,请记住将 ScreenUpdating 属性设回到 True。示例
本示例演示将屏幕更新关闭以后,系统如何加快代码的执行速度。本示例隔列隐藏 Sheet1 上的列,并保存其执行时间。第一次,示例隐藏列时,屏幕更新是打开的;第二次执行时,屏幕更新是关闭的。运行本示例时,可比较信息框中显示的两次执行时间。Dim elapsedTime(2)
Application.ScreenUpdating = True
For i = 1 To 2
If i = 2 Then Application.ScreenUpdating = False
startTime = Time
Worksheets("Sheet1").Activate
For Each c In ActiveSheet.Columns
If c.Column Mod 2 = 0 Then
c.Hidden = True
End If
Next c
stopTime = Time
elapsedTime(i) = (stopTime - startTime) * 24 * 60 * 60
Next i
Application.ScreenUpdating = True
MsgBox "Elapsed time, screen updating on: " & elapsedTime(1) & _
" sec." & Chr(13) & _
"Elapsed time, screen updating off: " & elapsedTime(2) & _
" sec."