Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Excel VBA
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:-
1. Open a new Excel instance.
2. Press ALT + F11/ Open VBE
3. Insert a new Module
4. 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.
Posted 31-Jul-12 20:21pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Im no expert in Special cells but it looks as you are making some "special" use of them according to the documentation:
 
http://msdn.microsoft.com/en-us/library/office/bb178148%28v=office.12%29.aspx[^]
 
And in some cases youll get an exeption:
http://social.msdn.microsoft.com/Forums/eu/vsto/thread/f7459dbc-0737-4d42-a5dd-011cc1c25206[^]
 
The range documentation:
http://msdn.microsoft.com/en-us/library/office/ff196157.aspx[^]
 
If you still cant make it work you should post you question at the designated VSTO forum [^] as most programemrs are not used to Excel manipulation.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.141015.1 | Last Updated 1 Aug 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100