SpecialCells方法的Excel范围奇怪的行为 [英] SpecialCells method of Excel Range strange behaviour
问题描述
我已经编写了一个宏,用特殊范围对象的SpecialCells方法从一定范围内找到空白单元格。
当我尝试执行以下代码时,我会收到一个例外:找不到单元。
子测试)
Debug.Print Sheet1.Range(A1:D4)。SpecialCells(xlCellTypeBlanks).Address
End Sub
步骤: -
- 打开一个新的Excel实例。
- 按ALT + F11 /打开VBE
- 插入新模块
- 粘贴以上代码,并按上述方式运行测试宏。 >
已收到的输出:
运行时错误'1004' 。没有找到单元格。
输出预期:
$ A $ 1:$ D $ 4
案例1:
现在如果我对单元格A1进行任何操作。操作,例如给予填充颜色等。调用test()宏,然后它不会抛出异常。
已收到的输出:
$ A $ 1
预期输出
$ A $ 1:$ D $ 4
案例2:
<假设我在单元格B3中给出任何值。调用test()宏,异常不会抛出。
已收到的输出
$ B $ 1:$ B $ 2,$ A $ 1:$ A $ 3
预期输出
$ A $ 1:$ D $ 4
案例3:
如果我尝试编辑值或填充范围A1:D4以外的单元格,例如E10,然后执行test()方法,那么它给出了正确的输出。
收到的收益:
$ A $ 1:$ D $ 4
原因是 SpecialCells $在返回范围时,c $ c>look在
UsedRange
中。
所以使用 SpecialCells
在未使用的工作表上将返回运行时错误'1004'。没有找到单元格。
(除此之外总是使用SpecialCells使用错误处理)
更改单元格A1时,这将成为 UsedRange
的表单,因此您的Case 1返回A1
下面的代码返回范围(A1:D4)。
- 空白表 - 失败
- A10填写的表格 -
A1:A4
- 清除A10的工作表 -
A1:A4
-
具有重新设置(原来是1)的工作表失败
Sub Test()
Dim ws As Worksheet
Set ws = Sheets.Add
On Error Resume Next
'blank sheet
Debug.PrintBlank sheet& ws.Range(A1:D4)SpecialCells(xlCellTypeBlanks).Address
'in A10
ws。[a10] =test
Debug.PrintWith A10& ; ws.Range(A1:D4)。SpecialCells(xlCellTypeBlanks).Address
'clear a10
ws。[a10] .ClearContents
Debug.PrintWith A10 clearing& ws.Range(A1:D4)。SpecialCells(xlCellTypeBlanks).Address
'reset range
ActiveSheet.UsedRange
Debug.PrintA10清零和范围重置& ws.Range(A1:D4)SpecialCells(xlCellTypeBlanks).Address
错误GoTo 0
End Sub
/ li>
I have written a macro to find blank cells from a certain range using SpecialCells method of excel range object. When i am trying to execute following code i get a exception as "No cells found".
Sub test()
Debug.Print Sheet1.Range("A1:D4").SpecialCells(xlCellTypeBlanks).Address
End Sub
Steps:-
- Open a new Excel instance.
- Press ALT + F11/ Open VBE
- Insert a new Module
- Paste the above code and run test macro as specified above.
Output Received:
Runtime Error '1004'. No Cells Found.
Output Expected:
$A$1:$D$4
Case 1:
Now if i do any operation of cell A1. Operation such as giving fill color, etc. Call the test() macro then it does not throw exception.
Output Received:
$A$1
Output Expected:
$A$1:$D$4
Case 2:
Suppose if i give any value in cell B3. Call the test() macro, exception is not thrown.
Output Received:
$B$1:$B$2,$A$1:$A$3
Output Expected:
$A$1:$D$4
Case 3:
If i try to edit value or fill the cell outside the range "A1:D4" for example E10 and execute test() method then it gives me the proper output.
Output Received:
$A$1:$D$4
Note: Execute each Case with a new excel instance.
The reason is SpecialCells
"looks" at the UsedRange
when returning a range.
So using SpecialCells
on an unused sheet will return Runtime Error '1004'. No Cells Found.
(As an aside always use error handling with SpecialCells)
When you change cell A1, that becomes the sheets UsedRange
, hence your Case 1 return of "A1"
The code below looks to return Range("A1:D4").SpecialCells(xlCellTypeBlanks) for
- A blank sheet - fails
- The sheet with A10 filled in -
A1:A4
- The sheet with A10 cleared -
A1:A4
The sheet with the usedrange reset (which is effectively 1) - fails
Sub Test() Dim ws As Worksheet Set ws = Sheets.Add On Error Resume Next 'blank sheet Debug.Print "Blank sheet " & ws.Range("A1:D4").SpecialCells(xlCellTypeBlanks).Address 'enter in A10 ws.[a10] = "test" Debug.Print "With A10 " & ws.Range("A1:D4").SpecialCells(xlCellTypeBlanks).Address 'clear a10 ws.[a10].ClearContents Debug.Print "With A10 cleared " & ws.Range("A1:D4").SpecialCells(xlCellTypeBlanks).Address 'reset range ActiveSheet.UsedRange Debug.Print "With A10 cleared and range reset" & ws.Range("A1:D4").SpecialCells(xlCellTypeBlanks).Address On Error GoTo 0 End Sub
这篇关于SpecialCells方法的Excel范围奇怪的行为的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!