Using CriterialRange with a condition

[cjaye]

Using an Excel 2003 macro.
I'm trying to copy a range of unique names (POCs point of contacts) to another sheet and it works fine. But what I'd like to do is copy the unique names to the other sheet only IF there is a "YES" in the next column (called Send Notification). I'm using Ron Debruin's excellent code in his "Mail a row or rows to each person in a range (SendMail).

Here's is a snippet. Let me know if you need more information

Please Login or Register  to view this content.

Can someone help me out on this please. I'm a struggling newbie to vb.

Thank you in advanced.

cjaye

[rylo]

Hi

Try adding a criteria range that has the column heading and YES and include that in your code. Depending on how you want to do things, you could have the code create it, then clear it when finished.

rylo

[cjaye]

Thank you for your response.

That's what I was thinking, I just can't quite get the syntax for it. It seems "criteriarange" needs to have a range and what I want the criteria to be is a range where the string "YES" exisits. I've tried many variations but keep getting either type mismatch errors of object doesn't suport this property or method.

Please Login or Register  to view this content.

[rylo]

Hi

The criteria range has to be a separate range. So if you have it as say Z1:Z2 where Z1 is the heading that is in the "YES" column and Z2 is YES. If that doesn't make sense, then attach an example workbook so we can see what you are working with and can structure the code to suit.


rylo

[cjaye]

Here is a very simplified example: If there is a YES in the Send notification column (E) then it will copy all the unique names to another worksheet and eventually emails the notifications out of the missing mileages. (and all that works good)

Veh# curr mileage prev mileage poc Send notification?
1234 1000 J.Doe YES
2444 2003 D.Parks YES
2323 1109 1120 D.Parks
4545 1249 1509 L.Redd

Here's is a code snippet

Please Login or Register  to view this content.

Any help would be greatly appreciated. And an example would be even better. I'm very new to vb and am really spinning my wheels.

[cjaye]

Sorry the sample spreadsheet is messed up. I tried installing an addin but kept getting errors about missing files when I tried to use it.

[rylo]

Hi

See how this goes.

rylo