|
|
本帖最后由 KCFONG 于 2012-12-11 07:40 编辑
大家都遇到 SUMPRODUCT函数 運行太慢, | 原因之一, 是因为源资料范围未能动态设定, | 大家常把公式的范围设定很大 | 构思一个宏, 可以动态设定源资料范围 | 把如下的宏放在 ThisWorkbook 底下的模块 | 当激活工作表, 宏会动态设定源资料范围 (加大100行) | 注意的是, 1.若源资料范围很大 或太多公式, 運行还是会很慢 2.不能 动态设定其它工作簿的源资料范围 |
多谢lee1892提点, 更新宏提高效率
Public dic As New Dictionary
'引用 c:\windows\system32\scrrun.dll
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Needupdate As Boolean
Application.EnableEvents = False: Application.Calculation = xlCalculationManual
For Each s In ThisWorkbook.Sheets
If s.UsedRange.Rows.Count <> 1 Then
LASTROW = s.Cells.SpecialCells(xlLastCell).Row
If dic(s.Name) <> LASTROW + 100 Then
dic(s.Name) = LASTROW + 100
Needupdate = True
End If 'For normal update
End If
Next
If Needupdate = False Then Exit Sub
Set ss = ActiveSheet.UsedRange.Find(What:="SUMPRODUCT", LookIn:=xlFormulas, Lookat:=xlPart)
If Not ss Is Nothing Then
sr = ss.Row: sc = ss.Column
Set ss1 = ActiveSheet.UsedRange.FindPrevious(After:=ss): er = ss1.Row: ec = ss1.Column
rng = Range(Cells(sr, sc), Cells(er, ec)).Formula
For Each SSS In Range(Cells(sr, sc), Cells(sr, ec))
yy = SSS.Formula
If yy Like "*SUMPRODUCT*" Then
X = Array("(", ")", "*", "=", ">", "<")
For i = 0 To UBound(X)
yy = Replace(yy, X(i), ",")
Next i
ZZ = Split(yy, ",")
For i = 0 To UBound(ZZ)
If InStr(ZZ(i), ":") <> 0 Then
SNAME = ActiveSheet.Name
If InStr(ZZ(i), "!") <> 0 Then SNAME = Left(ZZ(i), InStr(ZZ(i), "!") - 1)
If IsNumeric(Right(ZZ(i), 1)) Then
If dic.EXISTS(SNAME) Then REP = Left(ZZ(i), InStrRev(ZZ(i), "$")) & dic(SNAME)
ElseIf IsNumeric(Right(ZZ(i), 1)) = False Then ' A:D
REP = Replace(ZZ(i), ":", "$1:") & "$" & dic(SNAME)
End If
If REP <> "" Then
For R = 1 To UBound(rng)
For c = 1 To UBound(rng, 2)
rng(R, c) = Replace(rng(R, c), ZZ(i), REP)
Next c
Next R
End If
End If
Next i
End If
Next SSS
Range(Cells(sr, sc), Cells(er, ec)) = rng
End If
Application.EnableEvents = True: Application.Calculation = xlCalculationAutomatic
End Sub
|
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2012-12-10 21:56
发表于 2012-12-10 22:50
楼主
