Excel Run-Time Error 1004 No cells were found

J

Jnat2

New Member
Joined
Sep 25, 2008
Messages
9
This macro starts out looking for the first non blank cell by looking down about a hundred cells and then going back up till it finds the first non blank cell. When it finds the first non-blank cell it goes up 1 row and adds 1 to the number for the next row. It works fine the first time then, after the file is saved it will stop the macro with the following message; Run-time error ‘1004’ No cells were found. If I format the cells in question, the macro works again until I save the file, then it happens again.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
Excel 2003 11.8220.8221 SP3
I think there is a hotfix for this but I can't get it to install. Is there a way around this?

This is the portion of the code that gets the error:

Range("A11:A100").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
 ActiveCell.FormulaR1C1 = "=+R[-1]C+1"

Thanks
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Hello and welcome to MrExcel.

Try

Code: 
On Error Resume Next
Range("A11:A100").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=+R[-1]C+1"
On Error GoTo 0
 
Upvote 0
Thanks for responding.
I replaced with your sugestion. I adds the rows but now the macro doesn't work correctly. Did I insert in the correct place?

This is the entire Macro. It sshould look for the first blank row, then go right, puse for input, etc, etc, then return to menu.

Sheets("Smith_Ron").Select
Range("A1").Select
On Error Resume Next
Range("A11:A100").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=+R[-1]C+1"
On Error GoTo 0
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = InputBox(" ")
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = InputBox(" ")
ActiveCell.Offset(-1, 1).Range("A1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = InputBox(" ")
ActiveCell.Offset(-1, 1).Range("A1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = InputBox(" ")
Application.Run "Golf2009.xls!SelectMenu"
Application.Run "Golf2009.xls!Imput"
 
Upvote 0
I'm trying not to be negative but I don't think that I have ever seen code like this

Code: 
ActiveCell.Offset(1, 0).Range("A1").Select

You should not need to Select or Activate a range in order to work with it. Please explain in words exactly what your code is meant to do and a better solution is almost certainly available.
 
Upvote 0
LOL I don't know? But it worked.

This is a simple program to enter golf scores for various players. The first column is the number of rounds, the second is the date, then that days points, then last ten cumulative, then averaged by last ten, then money won/lost, then cumulative money, then if away course if away from home.

I think I got it by recording a macro in excel.

Thanks again.
 
Upvote 0
I'm guessing here:

Code: 
Sheets("Smith_Ron").Select
Range("A1").Select
On Error Resume Next
Range("A11:A100").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=+R[-1]C+1"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = InputBox(" ")
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = InputBox(" ")
ActiveCell.Offset(-1, 1).Range("A1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = InputBox(" ")
ActiveCell.Offset(-1, 1).Range("A1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = InputBox(" ")
On Error GoTo 0
Application.Run "Golf2009.xls!SelectMenu"
Application.Run "Golf2009.xls!Imput"
 
Upvote 0
When I run your changes, the macro always starts at A1 rather then the first non-blank cell in column A, which is the original problem where the macro sometimes fines it and sometimes not. Do you think it is an excel problem.

This is what I thought the Hotfix was for, but since I'm running SP3 it seems it should have been fixed.

When you programmatically count the dependent cells of a cell that is in a data table more than one time, the macro does not count the cells. Additionally, you receive the following error message: <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Run-time error '1004':

No cells were found.<o:p></o:p>
 
Upvote 0
You are not working with dependent cells, so that hotfix is unrelated. The error you are getting is due to excel not finding any blank cells in the range you specify.
 
Upvote 0
But there are blank cells, when I reformat the cells to a number format, the macro works fine. I save the file , reopen, run the macro again, and the I get the same error???
 
Upvote 0
Do you have anything in column A underneath your data? If not, you could simply use something like:
Code: 
   Dim rngNext As Range
   With Sheets("Smith_Ron")
      Set rngNext = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
   End With
   With rngNext
      .FormulaR1C1 = "=+R[-1]C+1"
      .Offset(0, 1).Value = InputBox(" ")
      .Offset(0, 2).Value = InputBox(" ")
      .Offset(0, 3).Value = .Offset(-1, 3).Value
      .Offset(0, 4).Value = .Offset(-1, 4).Value
      .Offset(0, 5).Value = InputBox(" ")
      .Offset(0, 6).Value = .Offset(-1, 6).Value
      .Offset(0, 7).Value = InputBox(" ")
   End With
   Application.Run "Golf2009.xls!SelectMenu"
   Application.Run "Golf2009.xls!Imput"

You'll need to test this to ensure I got the right cells!! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
How to Avoid Run Time Error 1004 without using On Error Resume Next
Replies
5
Views
371
NimishK
N
A
  • Solved
Error 1004 no cells found
Replies
3
Views
375
Arcadian
A
F
  • Question
No cells were found - trying to copy a hidden row range
Replies
3
Views
103
Micron
M
S
  • Solved
Run-time error '1004': No cells were found.
Replies
4
Views
621
sspatriots
S
K
  • Question
Variable losing set Range
Replies
8
Views
131
MARK858
MARK858

Forum statistics

Threads
1,215,836
Messages
6,127,175
Members
449,368
Latest member
JayHo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top