Getting run-time error 5631 no merge document or no option match your query option

Hi friends,

If there is remark ‘Send’ in column ‘W’ ‘REMARK L’ then I want to generate letter
If there is remark ‘Sent’ in column ‘W’ ‘REMARK L’ then I don’t want to generate letter

The problem is …
If there is one or more ‘Send’ remarks in column ‘W’ ‘REMARK L’ column then it works fine but if there is ‘Sent’ remark in column ‘W’ ‘REMARK L’ then I get a error 5631 ‘no merge document of the record didn’t match query option’

Requirement …
If there is no ‘Send’ remark in column ‘W’ ‘REMARK L’ and by mistake the user runs the macro then I wants to pop up warning message and exit sub.

If there is one or more ‘Send’ remarks in column ‘W’ then it works fine. With the kind guidance of Paul I have develop this macro.

Please Login or Register  to view this content.

Any help will be highly appreciated.

Thanking you in anticipation.

You have two different macros there. Which one are you using?

Also, do you have a pathological dislike for properly-structured code? I posted code with an intelligible structure in your other thread and now you're posting it back with a messed-up structure that looks like your dog dragged it around the neighbourhood for a few days.

As for the problem itself, you could obviate that by using Excel's 'Find' method to check whether there are any 'Send' records in the nominated range.

Hi Paul,

Sorry! I like the code you provided very well and using it. Please don’t mind. After exploring; I observed a error 5631 if there is no ‘Send’ remark in column ‘W’. So I thought to solve. Yes, it was the wrong attempt to solve the problem. As per your guidance now I used ‘Find’ method and amended the code. It’s running without error as per requirement. Please see and suggest me if anything is wrong. Is it a right method of use ‘Find’ method?

The amended code is:

Please Login or Register  to view this content.

Sorry for trouble.

Thanking you,

That's not how I'd implement the Find. Your implementation looks for 'Send' anywhere in column W, whereas you're only concerned with whether 'Send' occurs in the record range chosen by the user. Therefore your Find should occur after the user has input the record details and be limited to that range. For example, if the user chooses to merge records 100-200, your find should check W101-W201.

Hi Paul,

Thank you.

Like this?

Please Login or Register  to view this content.

Is this correct or not?

Thank you and have a nice time.

No, because you're still not testing the nominated range; you're still testing the whole column.

Hi Paul,

Oops! Is it correct now? If not correct me please.

Please Login or Register  to view this content.

Thanking you,

There's no point continually asking me if things are right when you've clearly ignored what I wrote in post #4. This code sequence:
Set rng1 = Range("W:W").Find(strSearch, , xlValues, xlWhole)
x = InputBox("What is the first record to merge?", , 1)
y = InputBox("What is the last record to merge?", , lRec)
can never test the nominated range, because:
1. You don't even get the nominated range until after rng1 is set; and, because of this
2. You don't (and can't) supply the nominated rows for the Range parameter.

Hi Paul,

Sorry! Due to my misunderstanding I didn’t follow your instruction. Now I understood that the range1 should be the value of inputbox X and Y. However, I don’t know how to set it. I was trying it but in vain.

Set rng1 = inputbox(“X”).value:inputbox(“Y”).value).Find(strSearch, , xlValues, xlWhole)

Please suggest me the correct way to set it.

Thanking you,

Originally Posted by mso3

Please suggest me the correct way to set it.

I already did that - in post #4!