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
>
>