Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Accumalate the email address existing in a single column into a single cell with each email add

  Asked By: Joel    Date: Mar 10    Category: MS Office    Views: 713
  

I have this problem. I am trying to accumalate the email address
existing in a single column into a single cell with each email
address separated by ;. I was able to do it. Now i want the single
cell pick up specific email addresses which have Yes condition in
the same row. ie i want to group this according to the condition
which exists in different column.

Right now i am using this but i am not able to achieve.

Function Chain(TargetCells As Range, ConditionCells As Range,
Optional Separator As Variant) As String
Dim Str As Range
For Each Str In TargetCells
If ConditionCells.value=Yes
Then
Chain = Chain & Separator & Str.Value
Next
Chain = Mid(Chain, Len(Separator) + 1)
Else
End If
End Function

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Raquel Austin     Answered On: Mar 10

Just use the OFFSET() function. For example, if the Yes/No condition
indicator is one column  to the right of the email  address:

> For Each Str In TargetCells
> If Str.Offset(0,1).Value = "Yes" then...

 
Answer #2    Answered By: Alarice Miller     Answered On: Mar 10

Thanks a lot that will help a lot.
I have do to some manupalation

 
Answer #3    Answered By: Frederik Jones     Answered On: Mar 10

Try the following revised function  (and a sample sub to call it):

Sub AAAA()
Dim strList As String
strList$ = Chain(Range("A1:A5"))
MsgBox strList$
End Sub

Function Chain(TargetCells As Range, Optional CondOffset = 1, Optional
Separator = ";") As String
'TargetCells is the range  of cells with e-mail addresses
'CondOffset is the number of columns difference between the TargetCells and the
condition cells. B=1, C=2, etc.
Dim c As Range, TmpStr As String
For Each c In TargetCells
If LCase(c.Offset(0, CondOffset).Value) = "yes" Then
TmpStr$ = c.Value & Separator
Else
TmpStr$ = vbNullString
End If
Chain$ = Chain$ & TmpStr$
Next
End Function

 




Tagged:                

 
 
 
 

Related Post