Microsoft Visual Basic Run-time error '1004' No cells were found

[Paul Riker]

I have a user that created a spreadsheet containing VBA. Whenever someone
else opens this spreadsheet and runs the macro it works fine. On his
computer, he get's this error message:

Microsoft Visual Basic
Run-time error '1004'
No cells were found

Sometimes Excel will say "Not Responding". This problem just recently
started happening. It was working fine before. Eventually, after retrying and
retrying the macro will work. He was getting an error before but I don't know
if it is related:

Event Type: Error
Event Source: DCOM
Event Category: None
Event ID: 10016
Date: 7/26/2005
Time: 9:38:36 AM
User: DAVID-TP50145\IWAM_DAVID-TP50145
Computer: DAVID-TP50145
Description:
The application-specific permission settings do not grant Local Activation
permission for the COM Server application with CLSID
{0C0A3666-30C9-11D0-8F20-00805F2CD064}
to the user DAVID-TP50145\IWAM_DAVID-TP50145 SID
(S-1-5-21-1653462319-2826650621-2974146706-1012). This security permission
can be modified using the Component Services administrative tool.

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.

I have since fixed this problem but his Excel issue is still there. Please
let me know if you can help.

Thanks

 

[Tim Williams]

What does the macro do?

Tim

 

[Paul Riker]

Here is how my user responded:

-User selects the department report from the SPH Intranet site
-Selection triggers an sql query against a table residing on CADE’s SQL
Server
-The results of the query are placed in a worksheet within an EXCEL
TEMPLATE. -This excel template contains the macros, formulas, and formats
that are used to create various views of the data.
-The EXCEL TEMPLATE, which is now populated with data, is sent to the users
desktop via the ‘SAVE†selection.
-The user activates the save file and initiates a macro by clicking on
“Click Here to Update and View Workbookâ€.

The Macro:
a) Date Stamps the views or worksheets so the user knows “Data as of …â€

b) Restructures Download File. The data resulting from the sql is not
formatted in the most efficient way. For example, there are multiple columns
of dollar values. The file has to be restructured so the data file has only
one column of dollar values. New columns are added via formulas and
equations and are built based on information in other columns. There is a
lot going on during this stage.

c) Builds 8 PivotTables

d) Formats 5 worksheets as views.

 

[Tim Williams]

What line of code generates the error ?

Tim

 

[Paul Riker]

'Get rid of Zero Records

Sheets("WorkingCopy2").Select

Cells.Select

Range("O1").Activate

Selection.Sort Key1:=Range("AD2"), Order1:=xlAscending, Header:=xlYes, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

Columns("AD:AD").Select

Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder _

:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

Columns("AD:AD").Select

Selection.SpecialCells(xlCellTypeBlanks).Select

Range(Selection, Cells(ActiveCell.Row, 1)).Select

Selection.Delete Shift:=xlUp

Cells.Select

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal



Cells.Select

Selection.Sort Key1:=Range("AE2"), Order1:=xlAscending, Header:=xlYes, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

Columns("AE:AE").Select

Selection.SpecialCells(xlCellTypeBlanks).Select

Range(Selection, Cells(ActiveCell.Row, 1)).Select

Selection.Delete Shift:=xlUp

 

[Tim Williams]

Any particular line?

Tim

 

[Paul Riker]

He said when he changes his reference from AD to AE it works fine. Again, his
computer is the only one affected. He is the one that developed them so could
it be an Excel setting?

 

[Tim Williams]

Could be. Hard to tell without seeing the sheet being operated on. He's
definitely using the same version of the code as the other users ?

Tim.

 

[Norman Jones]

Hi Paul,

The line:

Selection.SpecialCells(xlCellTypeBlanks).Select

Will fail with a run-time 1004 error if there are no blank cells in column
AD (of the used range).

My guess is that changing AD to AE works because column AE does have blank
cells.

Your user should add some apprpriate error handling, e.g:

Dim rng As Range
On Error Resume Next
Set rng = Columns("A").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rng Is Nothing Then
'Continue processing
Else
'Take apprpriate action, e.g.:
MsgBox "No blank cells found !"
End If

 

[Paul Riker]

The weird thing is that other users run the same macro on the same
spreadsheet and don't have any problems. This problem only occurs on his
computer after a reboot. After he tries it several times it eventually works,
without making any code changes.

 

[Norman Jones]

Hi Paul,

Whilst this may have no relevance for your user, if years were the number of
times that I have been assured categorically by a user of something that
subsequently transpired not to be so, I should either be dead or in the
Guiness Book of records.

With relevance to your user's code, I would expect the reported error if no
blank cells were discovered and, in any event, I would strongly advocate the
use of error trapping. Indeed, I would suggest that any use of the
SpecialCells method should always include a suitable error handler.

If the user's error truly is intermittent, without change of data or
circumstance, I regret that I have no additional suggestion,