Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > run-time error 1004 - VBA Gurus Please Help
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 26
Default run-time error 1004 - VBA Gurus Please Help
I am trying to set up a button that clears cells over several
worksheets.

But when it tries to switch to another worksheet, I get this error
-1004.

It say "select method of range class failed"

How can I correct this problem?

Here is the code I am using:

Sheets("Production").Select
Range("D4").Select
Selection.ClearContents
Range("F4").Select
Selection.ClearContents
Range("H4").Select
Selection.ClearContents
Range("D7:D10").Select
Selection.ClearContents
Range("F7:F10").Select
Selection.ClearContents
Range("H7:H10").Select
Selection.ClearContents
Range("N3").Select
Selection.ClearContents
Range("L6:Q13").Select
Selection.ClearContents
Range("D17:D24").Select
Selection.ClearContents
Range("F17:F24").Select
Selection.ClearContents
Range("H17:H24").Select
Selection.ClearContents
Range("M17:Q24").Select
Selection.ClearContents
Range("L29:Q38").Select
Selection.ClearContents
Range("B34:B38").Select
Selection.ClearContents
Range("E34:I38").Select
Selection.ClearContents
Range("B43:N51").Select
Selection.ClearContents
Sheets("Hours").Select
Range("D7:D12").Select (This is where the error occurs)
Selection.ClearContents
Range("F7:F12").Select
Selection.ClearContents
Range("G13:K13").Select
Selection.ClearContents
Range("M13:P13").Select
Selection.ClearContents
Range("D14:D19").Select
Selection.ClearContents
Range("F14:F19").Select
Selection.ClearContents
Range("G20:K20").Select
Selection.ClearContents
Range("M20:P20").Select
Selection.ClearContents
Range("D21:D26").Select
Selection.ClearContents
Range("F21:F26").Select
Selection.ClearContents
Range("G27:K27").Select
Selection.ClearContents
Range("M27:P27").Select
Selection.ClearContents
Range("D28:D33").Select
Selection.ClearContents
Range("F28:F33").Select
Selection.ClearContents
Range("G34:K34").Select
Selection.ClearContents
Range("M34:P34").Select
Selection.ClearContents
Range("C35:F40").Select
Selection.ClearContents
Range("G41:K41").Select
Selection.ClearContents
Range("M41:P41").Select
Selection.ClearContents
Range("C28:F33").Select
Selection.ClearContents
Range("C21:F26").Select
Selection.ClearContents
Range("C14:F19").Select
Selection.ClearContents
Range("C7:F12").Select
Selection.ClearContents
Range("Q7:U41").Select
Selection.ClearContents
Range("A46:X58").Select
Selection.ClearContents
Sheets("Salary Absentees").Select
Range("A5:C24").Select
Selection.ClearContents
Sheets("No Tires").Select
Range("B4:L40").Select
Selection.ClearContents
Sheets("Production").Select
Range("N3").Select

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,117
Default run-time error 1004 - VBA Gurus Please Help
the gurus just helped me with "select".
you can measurably shorten your code by using

Sheets("Production").Range("D4").ClearContents
(one line instead of three).

OR
With Sheets("Production")
.Range("D4").ClearContents
.Range("F4").ClearContents
'continue
End With

With xxxxxNextSheet
End With

as for why your button isn't working, where is the button located?
toolbar? activex control? userform?
susan

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 272
Default run-time error 1004 - VBA Gurus Please Help
Selection is not the best approach here. Try this:
With Sheets("Production")
.Range("D4").ClearContents
.Range(...).ClearContents
End With
With Sheets("Hours")
.Range("M27:P27").ClearContents
End With

That should run much faster.
--
Charles Chickering

"A good example is twice the value of good advice."


"SmartyPants" wrote:

I am trying to set up a button that clears cells over several
worksheets.

But when it tries to switch to another worksheet, I get this error
-1004.

It say "select method of range class failed"

How can I correct this problem?

Here is the code I am using:

Sheets("Production").Select
Range("D4").Select
Selection.ClearContents
Range("F4").Select
Selection.ClearContents
Range("H4").Select
Selection.ClearContents
Range("D7:D10").Select
Selection.ClearContents
Range("F7:F10").Select
Selection.ClearContents
Range("H7:H10").Select
Selection.ClearContents
Range("N3").Select
Selection.ClearContents
Range("L6:Q13").Select
Selection.ClearContents
Range("D17:D24").Select
Selection.ClearContents
Range("F17:F24").Select
Selection.ClearContents
Range("H17:H24").Select
Selection.ClearContents
Range("M17:Q24").Select
Selection.ClearContents
Range("L29:Q38").Select
Selection.ClearContents
Range("B34:B38").Select
Selection.ClearContents
Range("E34:I38").Select
Selection.ClearContents
Range("B43:N51").Select
Selection.ClearContents
Sheets("Hours").Select
Range("D7:D12").Select (This is where the error occurs)
Selection.ClearContents
Range("F7:F12").Select
Selection.ClearContents
Range("G13:K13").Select
Selection.ClearContents
Range("M13:P13").Select
Selection.ClearContents
Range("D14:D19").Select
Selection.ClearContents
Range("F14:F19").Select
Selection.ClearContents
Range("G20:K20").Select
Selection.ClearContents
Range("M20:P20").Select
Selection.ClearContents
Range("D21:D26").Select
Selection.ClearContents
Range("F21:F26").Select
Selection.ClearContents
Range("G27:K27").Select
Selection.ClearContents
Range("M27:P27").Select
Selection.ClearContents
Range("D28:D33").Select
Selection.ClearContents
Range("F28:F33").Select
Selection.ClearContents
Range("G34:K34").Select
Selection.ClearContents
Range("M34:P34").Select
Selection.ClearContents
Range("C35:F40").Select
Selection.ClearContents
Range("G41:K41").Select
Selection.ClearContents
Range("M41:P41").Select
Selection.ClearContents
Range("C28:F33").Select
Selection.ClearContents
Range("C21:F26").Select
Selection.ClearContents
Range("C14:F19").Select
Selection.ClearContents
Range("C7:F12").Select
Selection.ClearContents
Range("Q7:U41").Select
Selection.ClearContents
Range("A46:X58").Select
Selection.ClearContents
Sheets("Salary Absentees").Select
Range("A5:C24").Select
Selection.ClearContents
Sheets("No Tires").Select
Range("B4:L40").Select
Selection.ClearContents
Sheets("Production").Select
Range("N3").Select


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 26
Default run-time error 1004 - VBA Gurus Please Help

Susan wrote:
the gurus just helped me with "select".
you can measurably shorten your code by using

Sheets("Production").Range("D4").ClearContents
(one line instead of three).

OR
With Sheets("Production")
.Range("D4").ClearContents
.Range("F4").ClearContents
'continue
End With

With xxxxxNextSheet
End With

as for why your button isn't working, where is the button located?
toolbar? activex control? userform?
susan

The button is located on the actual sheet(production).

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,117
Default run-time error 1004 - VBA Gurus Please Help
The button is located on the actual sheet(production).

why don't you try changing that to a toolbar button or an autoshape
with the macro attached to it?
it could be (although i don't know a lot about activex) that since it's
attached to that first sheet, it won't let you move to another sheet.
susan

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 26
Default run-time error 1004 - VBA Gurus Please Help

Susan wrote:
The button is located on the actual sheet(production).

why don't you try changing that to a toolbar button or an autoshape
with the macro attached to it?
it could be (although i don't know a lot about activex) that since it's
attached to that first sheet, it won't let you move to another sheet.
susan

I have a "Print-All" button set up the same way and it works fine.

It's got to be the difference between printing and actively modifying a
cell.

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 272
Default run-time error 1004 - VBA Gurus Please Help
I'm guessing that the code is in the "Production" sheet code area. if that is
the case then Susan is correct, you cannot select another sheet from there.
You can however create a macro in a separate module and call that macro from
your button.
--
Charles Chickering

"A good example is twice the value of good advice."


"SmartyPants" wrote:


Susan wrote:
the gurus just helped me with "select".
you can measurably shorten your code by using

Sheets("Production").Range("D4").ClearContents
(one line instead of three).

OR
With Sheets("Production")
.Range("D4").ClearContents
.Range("F4").ClearContents
'continue
End With

With xxxxxNextSheet
End With

as for why your button isn't working, where is the button located?
toolbar? activex control? userform?
susan

The button is located on the actual sheet(production).


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,117
Default run-time error 1004 - VBA Gurus Please Help
Charles -
that's why you're a guru & i'm not! i didn't even think of
WHERE the code was stored! (i was on the right track
but didn't know why - ha ha).
:^D
susan

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 26
Default run-time error 1004 - VBA Gurus Please Help

Charles Chickering wrote:
I'm guessing that the code is in the "Production" sheet code area. if that is
the case then Susan is correct, you cannot select another sheet from there.
You can however create a macro in a separate module and call that macro from
your button.
--
Charles Chickering

"A good example is twice the value of good advice."



Sounds right... how do I call a macro from a button?

Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 272
Default run-time error 1004 - VBA Gurus Please Help
I gave you wrong information, you can modify another sheet from the sheet
code, however the problem is when you state Range("D2").Select from the sheet
code it thinks you mean sheets("Production").Range("D2") you need to specify
the parent object in order to fix this error.
Either:
Sheets("hours").Range("D2").Select
or:
With Sheets("hours")
.Range("D2").Select
End with

--
Charles Chickering

"A good example is twice the value of good advice."


"Charles Chickering" wrote:

I'm guessing that the code is in the "Production" sheet code area. if that is
the case then Susan is correct, you cannot select another sheet from there.
You can however create a macro in a separate module and call that macro from
your button.
--
Charles Chickering

"A good example is twice the value of good advice."


"SmartyPants" wrote:


Susan wrote:
the gurus just helped me with "select".
you can measurably shorten your code by using

Sheets("Production").Range("D4").ClearContents
(one line instead of three).

OR
With Sheets("Production")
.Range("D4").ClearContents
.Range("F4").ClearContents
'continue
End With

With xxxxxNextSheet
End With

as for why your button isn't working, where is the button located?
toolbar? activex control? userform?
susan

The button is located on the actual sheet(production).


Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 26
Default run-time error 1004 - VBA Gurus Please Help

Charles Chickering wrote:
I gave you wrong information, you can modify another sheet from the sheet
code, however the problem is when you state Range("D2").Select from the sheet
code it thinks you mean sheets("Production").Range("D2") you need to specify
the parent object in order to fix this error.
Either:
Sheets("hours").Range("D2").Select
or:
With Sheets("hours")
.Range("D2").Select
End with

--
Charles Chickering


SUCCESS!!! Thank You!

This is the new code:

Private Sub CommandButton4_Click()

Sheets("Production").Select
Range("D4").Select
Selection.ClearContents
Range("F4").Select
Selection.ClearContents
Range("H4").Select
Selection.ClearContents
Range("D7:D10").Select
Selection.ClearContents
Range("F7:F10").Select
Selection.ClearContents
Range("H7:H10").Select
Selection.ClearContents
Range("N3").Select
Selection.ClearContents
Range("L6:Q13").Select
Selection.ClearContents
Range("D17:D24").Select
Selection.ClearContents
Range("F17:F24").Select
Selection.ClearContents
Range("H17:H24").Select
Selection.ClearContents
Range("M17:Q24").Select
Selection.ClearContents
Range("L29:Q38").Select
Selection.ClearContents
Range("B34:B38").Select
Selection.ClearContents
Range("E34:I38").Select
Selection.ClearContents
Range("B43:N51").Select
Selection.ClearContents

Sheets("Hours").Range("G13:K13").ClearContents

Sheets("Hours").Range("M13:P13").ClearContents

Sheets("Hours").Range("G20:K20").ClearContents

Sheets("Hours").Range("M20:P20").ClearContents

Sheets("Hours").Range("G27:K27").ClearContents

Sheets("Hours").Range("M27:P27").ClearContents

Sheets("Hours").Range("G34:K34").ClearContents

Sheets("Hours").Range("M34:P34").ClearContents

Sheets("Hours").Range("C35:F40").ClearContents

Sheets("Hours").Range("G41:K41").ClearContents

Sheets("Hours").Range("M41:P41").ClearContents

Sheets("Hours").Range("C28:F33").ClearContents

Sheets("Hours").Range("C21:F26").ClearContents

Sheets("Hours").Range("C14:F19").ClearContents

Sheets("Hours").Range("C7:F12").ClearContents

Sheets("Hours").Range("Q7:U41").ClearContents

Sheets("Hours").Range("A46:X58").ClearContents

Sheets("Salary Absentees").Range("A5:C24").ClearContents

Sheets("No Tires").Range("B4:L40").ClearContents

Sheets("Production").Range("N3").Select


End Sub

Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default run-time error 1004 - VBA Gurus Please Help
If I was going to do what you are trying to do, I would assign a macro to the
button like this:
Sub commandbutton1_click()
clrContents
End Sub

Then in the general code module I would put the code you have written with
the name clrContents:

Sub clrContents()
With Sheets("Production")
.Range("D4").ClearContents
.Range("F4").ClearContents
(etc.)
End With
Sheets("Hours").Activate
With Sheets("Hours")
.(etc)
End Eith
(etc.)
End Sub




"SmartyPants" wrote:

I am trying to set up a button that clears cells over several
worksheets.

But when it tries to switch to another worksheet, I get this error
-1004.

It say "select method of range class failed"

How can I correct this problem?

Here is the code I am using:

Sheets("Production").Select
Range("D4").Select
Selection.ClearContents
Range("F4").Select
Selection.ClearContents
Range("H4").Select
Selection.ClearContents
Range("D7:D10").Select
Selection.ClearContents
Range("F7:F10").Select
Selection.ClearContents
Range("H7:H10").Select
Selection.ClearContents
Range("N3").Select
Selection.ClearContents
Range("L6:Q13").Select
Selection.ClearContents
Range("D17:D24").Select
Selection.ClearContents
Range("F17:F24").Select
Selection.ClearContents
Range("H17:H24").Select
Selection.ClearContents
Range("M17:Q24").Select
Selection.ClearContents
Range("L29:Q38").Select
Selection.ClearContents
Range("B34:B38").Select
Selection.ClearContents
Range("E34:I38").Select
Selection.ClearContents
Range("B43:N51").Select
Selection.ClearContents
Sheets("Hours").Select
Range("D7:D12").Select (This is where the error occurs)
Selection.ClearContents
Range("F7:F12").Select
Selection.ClearContents
Range("G13:K13").Select
Selection.ClearContents
Range("M13:P13").Select
Selection.ClearContents
Range("D14:D19").Select
Selection.ClearContents
Range("F14:F19").Select
Selection.ClearContents
Range("G20:K20").Select
Selection.ClearContents
Range("M20:P20").Select
Selection.ClearContents
Range("D21:D26").Select
Selection.ClearContents
Range("F21:F26").Select
Selection.ClearContents
Range("G27:K27").Select
Selection.ClearContents
Range("M27:P27").Select
Selection.ClearContents
Range("D28:D33").Select
Selection.ClearContents
Range("F28:F33").Select
Selection.ClearContents
Range("G34:K34").Select
Selection.ClearContents
Range("M34:P34").Select
Selection.ClearContents
Range("C35:F40").Select
Selection.ClearContents
Range("G41:K41").Select
Selection.ClearContents
Range("M41:P41").Select
Selection.ClearContents
Range("C28:F33").Select
Selection.ClearContents
Range("C21:F26").Select
Selection.ClearContents
Range("C14:F19").Select
Selection.ClearContents
Range("C7:F12").Select
Selection.ClearContents
Range("Q7:U41").Select
Selection.ClearContents
Range("A46:X58").Select
Selection.ClearContents
Sheets("Salary Absentees").Select
Range("A5:C24").Select
Selection.ClearContents
Sheets("No Tires").Select
Range("B4:L40").Select
Selection.ClearContents
Sheets("Production").Select
Range("N3").Select


Reply With QuoteReply With Quote
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 272
Default run-time error 1004 - VBA Gurus Please Help
JL, Why Activate the other sheet? It seems to me that it is unnecessary.
--
Charles Chickering

"A good example is twice the value of good advice."


"JLGWhiz" wrote:

If I was going to do what you are trying to do, I would assign a macro to the
button like this:
Sub commandbutton1_click()
clrContents
End Sub

Then in the general code module I would put the code you have written with
the name clrContents:

Sub clrContents()
With Sheets("Production")
.Range("D4").ClearContents
.Range("F4").ClearContents
(etc.)
End With
Sheets("Hours").Activate
With Sheets("Hours")
.(etc)
End Eith
(etc.)
End Sub




"SmartyPants" wrote:

I am trying to set up a button that clears cells over several
worksheets.

But when it tries to switch to another worksheet, I get this error
-1004.

It say "select method of range class failed"

How can I correct this problem?

Here is the code I am using:

Sheets("Production").Select
Range("D4").Select
Selection.ClearContents
Range("F4").Select
Selection.ClearContents
Range("H4").Select
Selection.ClearContents
Range("D7:D10").Select
Selection.ClearContents
Range("F7:F10").Select
Selection.ClearContents
Range("H7:H10").Select
Selection.ClearContents
Range("N3").Select
Selection.ClearContents
Range("L6:Q13").Select
Selection.ClearContents
Range("D17:D24").Select
Selection.ClearContents
Range("F17:F24").Select
Selection.ClearContents
Range("H17:H24").Select
Selection.ClearContents
Range("M17:Q24").Select
Selection.ClearContents
Range("L29:Q38").Select
Selection.ClearContents
Range("B34:B38").Select
Selection.ClearContents
Range("E34:I38").Select
Selection.ClearContents
Range("B43:N51").Select
Selection.ClearContents
Sheets("Hours").Select
Range("D7:D12").Select (This is where the error occurs)
Selection.ClearContents
Range("F7:F12").Select
Selection.ClearContents
Range("G13:K13").Select
Selection.ClearContents
Range("M13:P13").Select
Selection.ClearContents
Range("D14:D19").Select
Selection.ClearContents
Range("F14:F19").Select
Selection.ClearContents
Range("G20:K20").Select
Selection.ClearContents
Range("M20:P20").Select
Selection.ClearContents
Range("D21:D26").Select
Selection.ClearContents
Range("F21:F26").Select
Selection.ClearContents
Range("G27:K27").Select
Selection.ClearContents
Range("M27:P27").Select
Selection.ClearContents
Range("D28:D33").Select
Selection.ClearContents
Range("F28:F33").Select
Selection.ClearContents
Range("G34:K34").Select
Selection.ClearContents
Range("M34:P34").Select
Selection.ClearContents
Range("C35:F40").Select
Selection.ClearContents
Range("G41:K41").Select
Selection.ClearContents
Range("M41:P41").Select
Selection.ClearContents
Range("C28:F33").Select
Selection.ClearContents
Range("C21:F26").Select
Selection.ClearContents
Range("C14:F19").Select
Selection.ClearContents
Range("C7:F12").Select
Selection.ClearContents
Range("Q7:U41").Select
Selection.ClearContents
Range("A46:X58").Select
Selection.ClearContents
Sheets("Salary Absentees").Select
Range("A5:C24").Select
Selection.ClearContents
Sheets("No Tires").Select
Range("B4:L40").Select
Selection.ClearContents
Sheets("Production").Select
Range("N3").Select


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Run Time Error 1004: Application or Object Defined Error BEEJAY Excel Programming 4 October 18th 06 04:19 PM
Run Time 1004 Error: Application or Object Difine Error BEEJAY Excel Programming 0 October 17th 06 10:45 PM
Run-time error 1004 - General ODBC Error Linda Excel Programming 0 July 5th 06 04:32 PM
run-time error '1004': Application-defined or object-deifined error [email protected] Excel Programming 5 August 10th 05 09:39 PM
Run time error '1004': Generaol ODBC error Dwaine Horton[_3_] Excel Programming 2 April 26th 05 02:52 PM


All times are GMT +1. The time now is 05:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement