Range Question

[DejaVu]

I'm working on a program that will send out month end reports for me. I have a spreadsheet with all the report names (Subject Line for Email) and people that receive the emails (email addresses).

All the report names are lined up across row 1 in different columns, and the people are in column A in different rows. If the person is to receive a report, there will be an "X" for that person under the report.
I'm not sure how to explain this, but it confuses me reading this too...


This is a rough sketch of the layout of the report. We'll see if the spacing will work out:

________Report 1___Report 2___Report 3___Report 4
Bill_________X________X
Joe________X__________________X
Jim__________________X________X
Bob________X__________________X_________X

What I'm wanting to do is do some code that will look at each report or person (which ever is easier) and if there is an "X" there, then add the person to the string "MailTo".

I set my all of my reports to a range, and my people to a range.
Set RptRng = Range("B1", Selection.End(xlToRight))
Set MailRng = Range("A2", Selection.End(xlDown))


I hope this is a good enough explanation to warrant some help!!


TIA

DejaVu

[theDude]

This code should work for you. It will dynamically determine the number of reports and recipients, so if you add more reports and/or recipients to the matrix you won't have to change the macro code. Please delete the code line in BOLD below after you have tested it to ensure it works for you:

Please Login or Register  to view this content.

Hope this helps,
theDude

[DejaVu]

Impressive Dude, that was exactly what I was looking for. I always have troubles when it comes to dealing with the Range.

Thank you!!