检测纸张更换,清除纸张溢出 [英] Detecting sheet change, clearing the sheet giving an overflow
问题描述
我有一个 Worksheet_Change
事件,目前在工作表模块级别
。问题是我想能够清除这张表。但是,当我清除我的表单时,我会收到一个溢出:
Private Sub Worksheet_Change(ByVal Target As Range)
'这是导致问题的行,因为清除整个表格会导致计数大量
如果Target.Count = 1然后
如果Target = Range(A4)然后
如果InStr( LCase(Target.Value),loaded)< 0然后
Range(A5)。FormulaArray == My_Function(R [-1] C)
End If
End If
End If
End Sub
我正在尝试实现以下功能:
我按一个按钮,表格被清除(清除现有的数组公式数据),然后我将公式粘贴到工作表中并调用公式。该公式将数据返回到excel缓存,并将包含此公式(A4)的单元格更改为加载的字符串。当我检测到一个值为loaded的单元格更改时,我将在下面的数组公式函数上执行等价的 Ctrl + Shift + 输入
我相信你使用的是xl2007 +?
Target.Cells.Count
是一个 Long
值,因此当您选择整个工作表时, .Count
太小,无法保存结果。
替换行
如果Target.Count = 1然后
与
如果Target.Cells.CountLarge = 1然后
您可能还想看到此因为您使用 Worksheet_Change
编辑:
另外两件事>
1)
您也可以替换此行
如果Target = Range(A4)然后
与
如果不相交(目标,范围(A4))没有,然后
2)
行
If InStr(LCase(Target.Value),loaded)< 0然后
也可以写成
如果InStr(1,Target.Value,loaded,vbTextCompare)然后
I have a Worksheet_Change
event, currently at the Sheet Module Level
. The problem is that I want to be able to clear this sheet at times. However, when I clear my sheet I get an overflow:
Private Sub Worksheet_Change(ByVal Target As Range)
'This is the line causing the problem because clearing the whole sheet causes the count to be massive
If Target.Count = 1 Then
If Target = Range("A4") Then
If InStr(LCase(Target.Value), "loaded") <> 0 Then
Range("A5").FormulaArray = "=My_Function(R[-1]C)"
End If
End If
End If
End Sub
I am trying to achieve the following:
I press a button and the sheet is cleared (clears existing array formula data), I then paste in a formula to the sheet and call the formula. The formula returns data back to the excel cache and changes the cell containing this formula (A4) to a string saying "loaded". When I detect a cell change with value "loaded" I then do the equivalent on Ctrl + Shift + Enter on an array formula function below, to display the data.
I believe you are using xl2007+?
The Target.Cells.Count
is a Long
value and hence when you select the entire worksheet the .Count
is too small to hold the result.
Replace line
If Target.Count = 1 Then
with
If Target.Cells.CountLarge = 1 Then
You might also want to see this since you are using Worksheet_Change
EDIT:
Two other things
1)
You can also replace this line
If Target = Range("A4") Then
with
If Not Intersect(Target, Range("A4")) Is Nothing Then
2)
This line
If InStr(LCase(Target.Value), "loaded") <> 0 Then
can also be written as
If InStr(1, Target.Value, "loaded", vbTextCompare) Then
这篇关于检测纸张更换,清除纸张溢出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!