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

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

What does the macro do?

Tim

--
Tim Williams
Palo Alto, CA


"Paul Riker" wrote in message
...
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

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

'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

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

Any particular line?

Tim

--
Tim Williams
Palo Alto, CA


"Paul Riker" wrote in message
...
'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

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

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?

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

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" wrote in message
...
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?

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

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" wrote in message
...
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?

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

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.

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

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" wrote in message
...
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.