Hi there,

Say I have a range A1:A100. How, when clicking a command button, do I
get excel to loop through the range adding the contents into a string
with a colon between each entry?

e.g.

A
1
2
3
4

Becomes ;pete@pete .com"


Many thanks,

David

dim myRange as range, r as range
dim aWS as worksheet
Dim myString as string

set aWS = activesheet

myString = ""
set myRange = aWS.range("A1:A100")
for each r in myrange
if not isempty(r) then
mystring = mystring & ";" & r.value
end if
next r






next r

"Daveo" wrote:

Hi there,

Say I have a range A1:A100. How, when clicking a command button, do I
get excel to loop through the range adding the contents into a string
with a colon between each entry?

e.g.

A
1
2
3
4

Becomes ;pete@pete .com"


Many thanks,

David

Maybe something like this:

Paste this code into a General Module
Note: change the "MySheet" reference to the name of your worksheet.

'-------Start of Code---------------
Sub BuildEmailString()
Dim rCell As Range
Dim strText As String

If MsgBox(Title:="IMPORTANT", _
Prompt:="This program inserts the email list in the active cell." _
& vbCr & vbCr & "OK to continue?", _
Buttons:=vbQuestion + vbYesNo) _
= vbNo Then
Exit Sub
End If

strText = ""

For Each rCell In Worksheets("MySheet").Range("A1:A100").Cells
If InStr(1, rCell.Text, "@") 0 Then
strText = strText & rCell.Text & ";"
End If
Next rCell
ActiveCell.Value = Left(strText, Len(strText) - 1)
End Sub
'-------End of Code---------------

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Daveo" wrote:

Hi there,

Say I have a range A1:A100. How, when clicking a command button, do I
get excel to loop through the range adding the contents into a string
with a colon between each entry?

e.g.

A
1
2
3
4

Becomes ;pete@pete .com"


Many thanks,

David

Thanks folks - got it sorted with your help!

David