Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Question
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 -- DejaVu ------------------------------------------------------------------------ DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629 View this thread: http://www.excelforum.com/showthread...hreadid=377095 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Question
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: Code: -------------------- Sub sendReports() Dim repCount As Integer, nameCount As Integer Dim x As Integer, y As Integer Dim MailTo As String ' Determine number of reports to send... repCount = ActiveSheet.UsedRange.Columns.Count ' Determine number of names to send reports to... nameCount = ActiveSheet.UsedRange.Rows.Count ' Initialize variable... MailTo = "" ' Loop through report recipient matrix & send reports... For x = 2 To repCount ' Skip 1 for name column For y = 2 To nameCount ' Skip 1 for header row ' Determine if report should be sent to recipient... If ActiveSheet.Cells(x, y) = "X" Then ' If true, update variable with recipient name & comma separator w/trailing space... MailTo = MailTo & ActiveSheet.Cells(y, 1) & ", " End If Next y ' Strip last comma separator from variable... MailTo = Left(MailTo, Len(MailTo) - 2) *MsgBox "E-Mail " & ActiveSheet.Cells(1, x).Value & " to: " & MailTo* '********** YOUR CODE TO E-MAIL REPORT GOES HERE ********** ' Clear variable for next report... MailTo = "" ' Loop to next column to get report recipients... Next x End Sub -------------------- Hope this helps, theDude -- theDude ------------------------------------------------------------------------ theDude's Profile: http://www.excelforum.com/member.php...o&userid=16550 View this thread: http://www.excelforum.com/showthread...hreadid=377095 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Question
Impressive Dude, that was exactly what I was looking for. I always have troubles when it comes to dealing with the Range. Thank you!! -- DejaVu ------------------------------------------------------------------------ DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629 View this thread: http://www.excelforum.com/showthread...hreadid=377095 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
range question | Excel Programming | |||
Range question | Excel Programming | |||
Used Range Question | Excel Programming | |||
Range.Formula and Range question using Excel Automation | Excel Programming |