|
本帖最后由 jkl86262123 于 2015-8-4 07:20 编辑
- Private Sub Worksheet_Change(ByVal Target As Range)
- On Error Resume Next
- Dim Rng As Range, R As Range, Tmp$
- Set Rng = Intersect(Target, Columns(2))
- If Not Rng Is Nothing Then
- Application.EnableEvents = False
- With CreateObject("vbscript.regexp")
- .Global = True
- .Pattern = "[\u4e00-\u9fa5]"
- For Each R In Rng
- If Len(R.Text) Then
- temp = .Replace(R.Text, "")
- If IsError(Evaluate(temp)) = False Then R.Offset(, 1).Value = Evaluate(temp)
- Else
- If R.Offset(, 3).Value <> "合计" Then R.Offset(, 1).Value = ""
- End If
- Next R
- End With
- Application.EnableEvents = True
- End If
- Set Rng = Nothing
- Set R = Nothing
- If Target.Column = 3 Then
- Application.EnableEvents = False
- Set Rng = Columns("C:C").SpecialCells(xlCellTypeFormulas, 23)
- Rng.Offset(, 2) = "合计"
- If Intersect(Rng, Target) Is Nothing And Target.Offset(, 2) = "合计" Then Target.Offset(, 2) = ""
- Application.EnableEvents = True
- End If
- End Sub
复制代码 同样的一段代码
操作是 先复制任意一行的数据,然后选中 sum公式 合计行(见图片),插入复制的单元格,EXCEL2013会偶然性的卡死,停止工作,未保存的东西会丢失,但是在excel2007里面 一切正常 这有可能是什么原因导致的?
13版会自动更新屏幕,象Api高亮13版不行,我想,Application.ScreenUpdating=false 加在最前,
=True 放到最后试试。
另外,中间计算结果放在变量里,最后一步一起赋值给单元格可能比在循环里逐个赋值好些。
|
-
|