运行宏后Excel不响应 [英] Excel not responding after running macro
本文介绍了运行宏后Excel不响应的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我使用以下代码将列从一张表复制到另一张,然后用空值替换空白单元格:
'复制如果员工
sourceSheet.Activate
范围(单元格(2,7),单元格(Rows.Count,7).End(xlUp))。选择
Selection.Copy
destSheet.Activate
范围(E2,Cells(Rows.Count,7))。PasteSpecial
对于范围内的每个单元格(E2,Cells(Rows.Count ,5))
如果Len(cell.Value)= 0然后
cell.Value =否
结束如果
当我使用 Range(E2,Cells(500,5))
替换for语句时,它正在工作好的。
可能是什么问题?我无法弄清楚。任何人都可以帮助我吗?
解决方案
尝试
对于范围内的每个单元格(E2,Cells(destSheet.UsedRange.Rows.Count,5))
如果Len(cell.Value)= 0则
单元格。 Value =No
End If
或者更优雅(可能要快一些) ,
Dim calcStatus As XlCalculation
calcStatus = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
对于范围内的每个单元格(E2:E& destSheet.UsedRange.Rows.Count)
如果Len(cell.Value) = 0然后
cell.Value =否
结束如果
Application.Calculation = calcStatus
Application.ScreenUpdating = True
I used the below code to copy a column from one sheet to another and then replace the blank cells with a Null value:
'Copying If Employee
sourceSheet.Activate
Range(Cells(2, 7), Cells(Rows.Count, 7).End(xlUp)).Select
Selection.Copy
destSheet.Activate
Range("E2", Cells(Rows.Count, 7)).PasteSpecial
For Each cell In Range("E2", Cells(Rows.Count, 5))
If Len(cell.Value) = 0 Then
cell.Value = "No"
End If
When I replace the for statement with Range("E2", Cells(500,5))
it is working fine.
What might be the problem? I was unable to figure out. Can anyone help me with this?
解决方案
Try
For Each cell In Range("E2", Cells(destSheet.UsedRange.Rows.Count, 5))
If Len(cell.Value) = 0 Then
cell.Value = "No"
End If
Or more elegantly (and probably a lot faster),
Dim calcStatus As XlCalculation
calcStatus = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cell In Range("E2:E" & destSheet.UsedRange.Rows.Count)
If Len(cell.Value) = 0 Then
cell.Value = "No"
End If
Application.Calculation = calcStatus
Application.ScreenUpdating = True
这篇关于运行宏后Excel不响应的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文