Solved

vba(excel) run time error 1004

Posted on 2004-08-10
3
1,446 Views
Last Modified: 2006-11-17
hi there
i have 2 programs
  i use the first one to test some code
   and the second is the entire code

now my code works good at the first program the code is here (it only delete the rows which has 0 at column 2)

dec = 0
For I = 24 To 233
    V = Sheet12.Cells(I, 2)
    If V = "0" Then
      Sheet12.Rows(I - dec).Select   'heres the problem
      Selection.Delete shift:=lup
      dec = dec + 1
    End If
Next I


but when i paste the same code  (i have the same object called sheet12 of course)
it says:
     Run time error 1004
   Select method of range class failed


:( please




0
Comment
Question by:BaTy_GiRl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
mvidas earned 20 total points
ID: 11765978
Hi BaTy_GiRl,

While there are better ways to do what you're doing, I'll just try and answer your question first off.  

It would give you that error if your Sheet12 object isn't selected first.  Could that be the problem?  

Now, a couple suggestions:
You may want to change that reference to Sheets("sheetname") instead of Sheet12
When deleting rows in a loop, it's easier to do  For i = 233 to 24 Step -1

Even if you stick to the Sheet12 reference, you can do the whole thing using

 Dim i As Long
 For i = 233 To 24 Step -1
  If Sheet12.Cells(i, 2) = "0" Then Sheet12.Rows(i).Delete shift:=xlUp
 Next i

If the last row in column B (233) portion of your code changes, you could change the For line to:
 For i = Sheet12.Range("B65536").End(xlUp).Row To 24 Step -1

Using the code this way, nothing is selected.  This will not only prevent 1004 errors like you have, but also makes it much quicker.

Matt
0
 

Author Comment

by:BaTy_GiRl
ID: 11767428


Thank you very much  Matt you have solved my life =)

of course do you know why vba  give that kind of errors, i was very confused because
in Visual basic those errors reference libraries when objects doesn`t exist


0
 
LVL 35

Expert Comment

by:mvidas
ID: 11767455
I think it gave that error just because the sheet wasn't selected.  Say you had Sheet1 selected, and you gave it the command Sheet12.Range("A1").Select, it wouldn't know what to do because you didn't first say Sheet12.Select. It wasn't a problem with the object, just the selection portion of the code
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Visual Basic Excel Formatting error 4 141
How does CurrentUser work? 10 41
Set email body to html using vbscript 6 71
Access2016 ignore hyphen in data entry or searches 5 43
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question