For Each in Range
 

I'm working on some code that will add names to a Mail To list.
I have a worksheet that looks like the following (ignore the
underscores):

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

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'm trying to do some sort of:
For Each Report in RptRng
If ________ = "X" then
add Person to MailToList

I hope my explanation is good enough! Any help is greatly
appreciated!

DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=377159

For Each in Range
 

Perhaps this will help.

Code:
--------------------

Sub Test()
Dim rngRpt As Range
Dim LastRow As Long
Dim c As Range
Dim I As Long
Set rngRpt = Range("B1", Range("IV1").End(xlToRight))
LastRow = Range("A65536").End(xlUp).Row

For Each c In rngRpt
For I = 2 To LastRow
If Cells(I, c.Column) = "X" Then
MsgBox Range("A" & I) & "-" & Cells(1, c.Column)
End If
Next I
Next c
End Sub
--------------------

I haven't included any code to actually add to the mailing list as I
don't know what method you are using for that.


--
Norie
------------------------------------------------------------------------
Norie's Profile: http://www.excelforum.com/member.php...o&userid=19362
View this thread: http://www.excelforum.com/showthread...hreadid=377159

For Each in Range
 

One way:

Dim rCell As Range
Dim MailToList As Collection
Set MailToList = New Collection
For Each rCell In Cells(2, 2).Resize( _
Cells(Rows.Count, 1).End(xlUp).Row - 1, _
Cells(1, Columns.Count).End(xlToLeft).Column - 1)
If UCase(rCell.Text) = "X" Then
On Error Resume Next
MailToList.Add Cells(rCell.Row, 1).Text, _
Cells(rCell.Row, 1).Text
On Error GoTo 0
End If
Next rCell




In article ,
DejaVu wrote:

I'm working on some code that will add names to a Mail To list.
I have a worksheet that looks like the following (ignore the
underscores):

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

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'm trying to do some sort of:
For Each Report in RptRng
If ________ = "X" then
add Person to MailToList

I hope my explanation is good enough! Any help is greatly
appreciated!

DejaVu

For Each in Range
 

Different approaches, both good answers.

Thanks to both of you for your help. I get confused (all too often)
when it comes to dealing with Range.

Thanks Again!


DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=377159