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

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.

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.

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

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

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?

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.

Dear Sir,

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