Solved: What Triggers This Message??

[Cyberdude]

I have a very long macro that I've been doing a lot of changes in, and suddenly at one point I get the message:
"Do you want to replace the contents of the destination cells?"
There is no indication where this message was triggered, but I'm guessing it's the consequence of a "Copy" operation (and there's lots of those). (Gee, I hate messages with no clues.) I reply Yes, and everything seems to continue OK. So it's become a nuisance message. Apparently no errors have occurred. Any ideas??

[Shazam]

That happened to me today. The problem for me was that in cell B1 I have a formula then I want to merge a cell with A1 So I merged B1 with A1 and it gave me the same message. That only happens when the cell is already populated. But for me there was nonthing in A1 so I click on A1 and there was this : ' a comma so I delete the comma and everything is fine.


Hopefuly that helps.

[Norie]

Can you post the code?

I don't think we can help you without that.

[Ken Puls]

Hi Sid,

I would assume (always a dangerous thing to do) that there is already data in the range that you are pasting to. I can't say that I've ever run into that particular message as the result of any code though...

If you just want to ignore it, you could just wrap the offending code in the following though:

[vba]Application.DisplayAlerts=False
'Your code here
Application.DisplayAlerts=True[/vba]

I would just make an attempt to use this on only as much of the code as you need messages suppressed in though, as you don't want to suppress one that you should know about.

HTH,

[Cyberdude]

To Shazam: You may be on to something. I'll look for possible copies into a merged cell.

To Norie: It would be quite difficult to post all the code because the base macro is very large and it calls MANY submacros. I'll do so sleuthing and see if I can narrow it down. One of my problems is that it's very difficult to run a test without affecting some sensitive data.

To Ken: Thanx for the suggestions. I've never had this message as a result of VBA code either. Suppressing the message at this stage is probably not a good idea because I'd have to put it almost just after the Sub statement. Although I could try suppressing it in just smaller sections until it doesn't occur. Then I'd at least have narrowed it down to a certain region. Good idea, Sid!

[Ken Puls]

Good idea, Sid!

Sid, you crack me up sometimes!

On the merged cell note, I don't think that would necessarily be it. Could be proven wrong though, but don't think so. Trying to paste over merged cells gives a different message, I believe, which cause the macro to fail. ie You couldn't just say Yes and continue on. I can't remember exactly what the message is, but I think it has something to do with the range being a consistent size & shape.

[Soapbox]As a general note on merged cells, I hate them. They are, IMHO, the Devil's gift to Excel and should be avoided at all costs. To my view, they only cause trouble. Most people seem to use them to accomplish centering across multiple cells, but this can be accomplished with the "Center Across Selection" in the formatting area to, and avoids many of the issues that can creep up later. [/Soapbox]

[Bob Phillips]

[Soapbox]As a general note on merged cells, I hate them. They are, IMHO, the Devil's gift to Excel and should be avoided at all costs. To my view, they only cause trouble. Most people seem to use them to accomplish centering across multiple cells, but this can be accomplished with the "Center Across Selection" in the formatting area to, and avoids many of the issues that can creep up later. [/Soapbox]

Seconded!

[Soapbox II]Only eclisped in direness by shared workbooks. [/Soapbox II]

[mvidas]

The only time I've ever seen that message is when doing a Text To Columns.
Put "1,2" in A1, and "text" in B1. Perform a text to columns on A1 with comma delimiter, and you'll see it.

[Cyberdude]

To Ken: It's nice knowing I can bring a grin to your face.
I'm amused about how adamant you guys are about something like merged cells. To cool you down, I don't have any valued merged cells in this particular system, so that's not the problem.

To Matt: I don't have any "Text to Columns" code, so that's not likely the problem.

I'm going to make a production run with this program in about 15 minutes from now. What I've done is put a "trace" to give me a clue about where this is happening. I spent a lot of time searching for it, but nothing seems "improper". Regarding the "Yes/No" character of the message, it accepts Yes, but if I say No, I just get the message again. What kind of choice is THAT! Phffft!

[Ken Puls]

Hey Sid,

40 minutes after posting my soapbox speach, I got a call about not being able to use an Excel data source for a mailmerge in Word. The culprit? All rows in row 1 were merged so that the user could centre the text. Got rid of the merged row, and it worked like a hot-damn!

I'll tell you, Bob's word to the world is to code using .rows.count to find the last row (not just hard code 65536), mine is on merged cells. They are EVIL.

[Bob Phillips]

I'm going to make a production run with this program in about 15 minutes from now. What I've done is put a "trace" to give me a clue about where this is happening. I spent a lot of time searching for it, but nothing seems "improper". Regarding the "Yes/No" character of the message, it accepts Yes, but if I say No, I just get the message again. What kind of choice is THAT! Phffft!

Have you tried evil #3, Sendkeys?

[Cyberdude]

OK, to finish this up, my trace yesterday showed me the problem. I had a simple copy of one column range to another. As luck would have it, I was off by one row for the top cell of the range to be copied. I had H24 and it should have been H25. So what? Well, ummmm, it turns out that cell H24 was in the middle of a merged cell that contained (what else) a text error message. So I did indeed have a text-to-column copy from a merged cell. The worst of all possible conditions. OK, I'm convinced. No more merged cells. Thanx for the help fellas!