Run Time Error 1004

[Stanlee]

Hi, I'm trying to run a macro but I am receiving the Run Time Error 1004: Application-defined or object-defined error. When the error appears it doesn't indicate the line of code that's causing the problem, but by running the two halves of the macro seperately I have identified the error to be in the second half of the code.

Please Login or Register  to view this content.

From reading other peoples' posts about this type of error I presumed it was being caused by the worksheet not being defined or activated, but if that is the cause I haven't been able to adequately resolve it. Would appreciate any assistance that could be provided.

Thanks in advance.

[MarvinP]

Hi Stanlee,

I wonder if you are trying to make your code work on the active worksheet or on the ws sheet? After doing your "Set ws = .." statement it looks like you want your for/next loop to work on the ws sheet.

To do this you need to use the WITH construct and a period in front of the Range word in your code. This is a very small change to your code but is very important to Excel VBA.

Example
For My_Rows = Range("Q4705").End(xlUp).Row To 11 Step -1 ' This code works on the active sheet or the one you are looking at

With ws
For My_Rows = .Range("Q4705").End(xlUp).Row To 11 Step -1 ' This code works on the ws that is set with the "With" statement
End With

I'm totally guessing what you are trying to do but feel this is what is happening.

[Stanlee]

Hi MarvinP, thanks for the quick response. The "Set ws = .." was a hangover from when I tried to define the active sheet and then define the range doing the mergining and selecting thinking that would sort the problem but unfortunately I still received the same run time error. I tried to put everything back the way it was but must have forgotten a couple of statements at the beginning. Below should be the original code:

Please Login or Register  to view this content.

Apologies for any confusion.