Excel VBA run-time error 1004 : Application-defined or object-defined error

Hello
I am writing a vba macro for my custom need. Below is a code line in this macro:

Worksheets(strt_stmp).Range(Cells(rw1, cl1), Cells(rw1 + 8, cl1)).Value = ""

(strt_stmp, rw1, cl1 are variables defined earlier)

The intent here is to clear the cell contents in the range.

I am facing a strange scenario. Sometimes when I run the macro, it goes through without any problem. However sometimes when I run it, I get Run-time error 1004: Application-defined or object-defined error.
Please help me in understanding the problem here.
Thank You in advance.

Regards,
Pawan
 
Question Info

Last updated July 19, 2018 Views 124,908 Applies to:

When you get the error again, go into DEBUG and determine the values for the variables involved and ensure that they are all legitimate values.  You may find that one is not valid - for example, rw1 might be zero, or it might be so large that rw1+8 is greater than the number of rows permitted for the worksheet.  It could also be that the active workbook doesn't contain a sheet named as whatever is in strt_stmp.

 

Your statement doesn't actually totally clear the contents of the cells, it places a zero length string into them.  To actually clear the contents you might consider using

 

Worksheets(strt_stmp).Range(Cells(rw1, cl1), Cells(rw1 + 8, cl1)).ClearContents

 

Another possibility might be that the worksheet is protected which would prevent the change to the values in those cells if they are locked.

I am free because I know that I alone am morally responsible for everything I do. R.A. Heinlein

13 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Hi,

 

If the variables are defined and have appropriate values then that will work if the worksheet strt_stmp is the activesheet. If it isn't the active sheet then you get the runtime error you describe.

If this response answers your question then please mark as answer.

Mike H

21 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

I'm having the same issue as the original poster. Is there way to access the range without having to activate the sheet?

Thanks for any help.

Nick

Did this solve your problem?

Sorry this didn't help.

Yes, you can reference any worksheet regardless of which one is active by specifically using it in the reference, as:

Worksheets("the_sheet_name_shown_on_its_tab").Range(range reference).property_or_method

As

cellA5Value = Worksheets("Sheet1").Range("A5")

.value is implied when used like that but you can be explicit

cellA5Value = Worksheets("Sheet1").Range("A5").Value

You can also reference them by their relative position in the workbook (and cross your fingers that a user doesn't move them around), as

cellA5Value = Worksheets(1).Range("A5")

would get the value of A5 in the first/left-most sheet in the workbook.

A little better way is to set an 'object' as a reference to the worksheet and then use that object when you want to reference it as:

Dim myWS As Worksheet

Set myWS = Worksheets("Sheet3")

then you can write code like this from that point

cellA5Value = myWS.Range("A5")

There's an even better way, using what is called the sheet's Code Name in your VBA code: code that uses this method will continue to work even if someone changes the name shown on a sheet's tab or moves it around in the workbook.  You can read about it in Section 2 (starts on page 2) of this little thing I put together a while back:

http://www.jlathamsite.com/Teach/VBA/WritingBulletProofCode.pdf

I am free because I know that I alone am morally responsible for everything I do. R.A. Heinlein

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Hi JLLatham,

I've been using the latter methond you decribed in your code to refer to a worksheet i.e.

Dim myWS As Worksheet

Set myWS = Worksheets("Sheet3")



It has worked for me up until recently. I've been trying to quite a large array (10000 rows, 37 columns)back to the worksheet but get the "Excel VBA run-time error 1004 : Application-defined or object-defined error" if the worksheet isn't selected; however it appears to run when the worksheet is selected. Do you have any ideas as to what could be going wrong/or how to fix this?

617 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

My macros are not working, for example our taxes we download them from the internet and then we find the template and validate and generate other worksheet which we submit. We are receiving the message which is saying "macro basic error 1004". Could I be restored to my previous setting because the computer was formated and thus the problem came. 

Looking forward to your quick response.

Regards,

Kenneth Ngalamika.

Did this solve your problem?

Sorry this didn't help.

Dear Sir,

Am waiting to hear from you soon so that I can make your service good.

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.