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

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

What does the macro do?

Tim

--
Tim Williams
Palo Alto, CA


"Paul Riker" <[email protected]> wrote in message
news:[email protected]...
> 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

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.

What line of code generates the error ?

Tim

--
Tim Williams
Palo Alto, CA


"Paul Riker" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

'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

Any particular line?

Tim

--
Tim Williams
Palo Alto, CA


"Paul Riker" <[email protected]> wrote in message
news:[email protected]...
> '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
>
>

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?

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.
--
Tim Williams
Palo Alto, CA


"Paul Riker" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

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:D").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


---
Regards,
Norman



"Paul Riker" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

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.

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,

---
Regards,
Norman



"Paul Riker" <[email protected]> wrote in message
news:[email protected]...
> 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.