Solved

vba(excel) run time error 1004

Posted on 2004-08-10
3
1,439 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now