Macro Run-time Error '1004' Application-defined or object-defined error

[alisoncleverly]

Hello everyone,

The file worked well until I changed some of the conditions in the ElseIf statements (ex: "Green" to "Yellow", etc) and the macro has stopped working since then. I've stuck with this problem for 2 weeks and still have no idea how to fix it. I'm taking over a former colleague's position and the macro was what he did back then. Please help! thank you very much!

Some highlights:

1. Originally, we extract data from SAP (that's why there's a sheet called SAP in the file) and then Paste as Value the data onto "Master Worksheet" sheet. The pasted data range from column A to M. In other words, we start with pasting (paste as value) the data to "Master Worksheet" which currently returns error message.

Debug pointed out the following line was the source of the error:

Please Login or Register  to view this content.

It totally makes sense to me that since production_cell is changed_cell, sales_cell would be 1 column to the left of production_cell/changed_cell (see column order in Master Worksheet).

Then why was it highlighted and how to fix this problem?

2. The Macro was written to automate every time cells in columns "Sales" or "Production" receive values (Green, Yellow, Overdue, etc), then the cell in columns "Day" will automatically return the corresponding value stated in those If and ElseIf statements. Here, the sample file has 3 Days columns (Day 1, Day 2, Day 3). In my original file, it has 8 days in total.

3. Is there a way to incorporate the If functions in each "Status" column with the Macro, because they might carelessly get deleted?

Thank you so much! Please help!

[Arkadi]

Ok so the worksheet change event passes the Target as an argument to your macro... but if you are pasting into columns A through M, then the column for "Target" is column 1. (or at least it starts in column 1). Your macro is called single_change, but in reality when you paste all that data into the sheet, you are changing a larger range, not one cell. sales_cell is meant to be 1 left of the production_cell, but production cell is A2:M15 and an offset of -1 would put sales_cell one column to the left of column A which is not possible, hence your error

[alisoncleverly]

Thanks a lot for your explanation. I understand the error, however, can you please suggest how I should change my codes since I have very little knowledge of VBA. Thanks again!

[Arkadi]

Essentially, we need to figure out when your worksheet change event should actually trigger, and WHERE it applies to. Should it run whenever a cell changes? If so, I assume it does not matter if one cell changed or many? BUT I also assume it should only apply to a specific column or columns?

For example, if you paste data from SAP to the master sheet, I get the feeling maybe what you want is that for each cell that changes you want to do something, but that it should only be looking at column O?

[alisoncleverly]

hi, precisely, the macro should apply to a certain range of columns, which in my case is Column N to the right. the macro runs whenever I paste (as value) the data from another file or worksheet to the "Master Worksheet". Do you know how I can do that? Thanks a lot!

[Arkadi]

Let's clarify. When you paste data, the macro should affect colums N and to the right... But a worksheet change event will trigger whenever something changes, so should it run any time you change any cell? Or only when you paste into a bigger area?

Your "Single change" code is given "changed_cell" as a range... what is that meant to be? sorry i did look at the code, but don't know enough about the intended outcome to quite sort it out. It may be best if you could explain what should happen exactly.

[alisoncleverly]

Sorry for the confusion.
1st, the macro starts when I paste data. The pasted data is always within column A - M.
2nd, the macro should affect columns N all the way to the right.
3rd, as you can see in the Sample file, I have 3 "Day" columns (Day 1, Day 2, and Day 3). Values are entered in those columns at different times. So, I believe it runs anytime I change a cell.
4th, regarding the "changed_cell" question you asked, I didn't write these codes so I can't really say what my former colleague had intended for. Though not very certain but I assume that he set "changed_cell" as the changing range, it could either be "Sales" columns or "Production" columns. No matter where the change is made first, Sales or Production, Day columns return the value from those if statements you saw.
Please let me know if it still confuses you and thank you!

[Arkadi]

As I read the code, I am starting to wonder...

Is there anything at all for the macro to do when you paste the initial data? The single_change macro seems to be intended to evaluate the value of the Sales and Production columns (within your "days"), and then update the "Day" column. When you first paste your data, none of those columns have values, and so the macro running at that point would just put a blank in the day cells (which is how you start off anyhow) .
Does that make sense?

I get the feeling more was changed than just If statements though... I can't see how the current code would ever have avoided the error you are getting at the moment.

Don't suppose you have a copy of the original code?

[alisoncleverly]

Hi, I replicated the Sample to be very alike to the original file, except for reducing those Sales, Production, and Day columns to 3 in total. The codes are the same. You`re absolutely right about the macro evaluating the values of Sales and Production columns. And yes, initially after pasting the data, someone has to manually evaluate the department`s business and then out down the value in Sales and Production columns. Can you suggest a way to get those If statements to avoid the error? In the original file, I have at least 100 If statements like that and I was told it`s not an efficient way. Thanks a lot for your help

[Arkadi]

I was trying to dive deeper into the code to make sure I didn't ruin any of the functionality... and I think the answer might be quite simple actually. It is not the most elegant, but I also don't understand the process enough to re-write it all. It seems to me the original author probably had one line of code above some of the new if statements. In your single_change sub, move this line to just below the Dim statements, meaning above the Ifs:

Please Login or Register  to view this content.