Add to a string to make it continuous
 

I currently have a spreadsheet that has an infinate number of rows determined
by the user.

The code states:

For row=7 to Worksheets('Example').UsedRange.Row.Count

String1 = Worksheets("Example").Cells.(Row,1).Value

Endfor

Now I am trying to accomplish it to just add to the string opposed to just
creating a new line each time. This information is finally outputted to Word.

For example: What I want
Column

Apples
Oranges
Peaches

to look like: Apples, Oranges, Peaches

What I get:

Apples, Oranges, Peaches
Apples, Oranges
Apples

is the current output. Any suggestions? Thanks

Add to a string to make it continuous
 

Adam,

Change
String1 = Worksheets("Example").Cells.(Row,1).Value
to
String1 = String1 & Worksheets("Example").Cells.(Row,1).Value


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Adam" wrote in message
...
I currently have a spreadsheet that has an infinate number of
rows determined
by the user.

The code states:

For row=7 to Worksheets('Example').UsedRange.Row.Count

String1 = Worksheets("Example").Cells.(Row,1).Value

Endfor

Now I am trying to accomplish it to just add to the string
opposed to just
creating a new line each time. This information is finally
outputted to Word.

For example: What I want
Column

Apples
Oranges
Peaches

to look like: Apples, Oranges, Peaches

What I get:

Apples, Oranges, Peaches
Apples, Oranges
Apples

is the current output. Any suggestions? Thanks

Add to a string to make it continuous
 

try
Sub stringem()
For Each C In Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row)
mystring = mystring & C & ","
Next
MsgBox Left(mystring, Len(mystring) - 1)

End Sub

--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
I currently have a spreadsheet that has an infinate number of rows
determined
by the user.

The code states:

For row=7 to Worksheets('Example').UsedRange.Row.Count

String1 = Worksheets("Example").Cells.(Row,1).Value

Endfor

Now I am trying to accomplish it to just add to the string opposed to just
creating a new line each time. This information is finally outputted to
Word.

For example: What I want
Column

Apples
Oranges
Peaches

to look like: Apples, Oranges, Peaches

What I get:

Apples, Oranges, Peaches
Apples, Oranges
Apples

is the current output. Any suggestions? Thanks

Add to a string to make it continuous
 

Sorry, but this function does not work. It still cascades it down showing

Apples, Oranges, Peaches
Apples, Oranges
Apples

"Chip Pearson" wrote:

Adam,

Change
String1 = Worksheets("Example").Cells.(Row,1).Value
to
String1 = String1 & Worksheets("Example").Cells.(Row,1).Value


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Adam" wrote in message
...
I currently have a spreadsheet that has an infinate number of
rows determined
by the user.

The code states:

For row=7 to Worksheets('Example').UsedRange.Row.Count

String1 = Worksheets("Example").Cells.(Row,1).Value

Endfor

Now I am trying to accomplish it to just add to the string
opposed to just
creating a new line each time. This information is finally
outputted to Word.

For example: What I want
Column

Apples
Oranges
Peaches

to look like: Apples, Oranges, Peaches

What I get:

Apples, Oranges, Peaches
Apples, Oranges
Apples

is the current output. Any suggestions? Thanks

Add to a string to make it continuous
 

This did not seem to work either.

"Don Guillett" wrote:

try
Sub stringem()
For Each C In Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row)
mystring = mystring & C & ","
Next
MsgBox Left(mystring, Len(mystring) - 1)

End Sub

--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
I currently have a spreadsheet that has an infinate number of rows
determined
by the user.

The code states:

For row=7 to Worksheets('Example').UsedRange.Row.Count

String1 = Worksheets("Example").Cells.(Row,1).Value

Endfor

Now I am trying to accomplish it to just add to the string opposed to just
creating a new line each time. This information is finally outputted to
Word.

For example: What I want
Column

Apples
Oranges
Peaches

to look like: Apples, Oranges, Peaches

What I get:

Apples, Oranges, Peaches
Apples, Oranges
Apples

is the current output. Any suggestions? Thanks

Add to a string to make it continuous
 

Did you try my suggestion?

--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
Sorry, but this function does not work. It still cascades it down showing

Apples, Oranges, Peaches
Apples, Oranges
Apples

"Chip Pearson" wrote:

Adam,

Change
String1 = Worksheets("Example").Cells.(Row,1).Value
to
String1 = String1 & Worksheets("Example").Cells.(Row,1).Value


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Adam" wrote in message
...
I currently have a spreadsheet that has an infinate number of
rows determined
by the user.

The code states:

For row=7 to Worksheets('Example').UsedRange.Row.Count

String1 = Worksheets("Example").Cells.(Row,1).Value

Endfor

Now I am trying to accomplish it to just add to the string
opposed to just
creating a new line each time. This information is finally
outputted to Word.

For example: What I want
Column

Apples
Oranges
Peaches

to look like: Apples, Oranges, Peaches

What I get:

Apples, Oranges, Peaches
Apples, Oranges
Apples

is the current output. Any suggestions? Thanks

Add to a string to make it continuous
 

Fully tested. How did you modify to suit your needs.

--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
This did not seem to work either.

"Don Guillett" wrote:

try
Sub stringem()
For Each C In Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row)
mystring = mystring & C & ","
Next
MsgBox Left(mystring, Len(mystring) - 1)

End Sub

--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
I currently have a spreadsheet that has an infinate number of rows
determined
by the user.

The code states:

For row=7 to Worksheets('Example').UsedRange.Row.Count

String1 = Worksheets("Example").Cells.(Row,1).Value

Endfor

Now I am trying to accomplish it to just add to the string opposed to
just
creating a new line each time. This information is finally outputted
to
Word.

For example: What I want
Column

Apples
Oranges
Peaches

to look like: Apples, Oranges, Peaches

What I get:

Apples, Oranges, Peaches
Apples, Oranges
Apples

is the current output. Any suggestions? Thanks

Add to a string to make it continuous
 

Don't write to word inside the loop. Build the entire string, then write to
word one time.

--
Regards,
Tom Ogilvy


"Adam" wrote in message
...
This did not seem to work either.

"Don Guillett" wrote:

try
Sub stringem()
For Each C In Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row)
mystring = mystring & C & ","
Next
MsgBox Left(mystring, Len(mystring) - 1)

End Sub

--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
I currently have a spreadsheet that has an infinate number of rows
determined
by the user.

The code states:

For row=7 to Worksheets('Example').UsedRange.Row.Count

String1 = Worksheets("Example").Cells.(Row,1).Value

Endfor

Now I am trying to accomplish it to just add to the string opposed to
just
creating a new line each time. This information is finally outputted
to
Word.

For example: What I want
Column

Apples
Oranges
Peaches

to look like: Apples, Oranges, Peaches

What I get:

Apples, Oranges, Peaches
Apples, Oranges
Apples

is the current output. Any suggestions? Thanks

Add to a string to make it continuous
 

I got your code to work. Thanks!!

"Don Guillett" wrote:

Fully tested. How did you modify to suit your needs.

--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
This did not seem to work either.

"Don Guillett" wrote:

try
Sub stringem()
For Each C In Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row)
mystring = mystring & C & ","
Next
MsgBox Left(mystring, Len(mystring) - 1)

End Sub

--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
I currently have a spreadsheet that has an infinate number of rows
determined
by the user.

The code states:

For row=7 to Worksheets('Example').UsedRange.Row.Count

String1 = Worksheets("Example").Cells.(Row,1).Value

Endfor

Now I am trying to accomplish it to just add to the string opposed to
just
creating a new line each time. This information is finally outputted
to
Word.

For example: What I want
Column

Apples
Oranges
Peaches

to look like: Apples, Oranges, Peaches

What I get:

Apples, Oranges, Peaches
Apples, Oranges
Apples

is the current output. Any suggestions? Thanks

Add to a string to make it continuous
 

The latest versions of Excel might be able to do it this way. Just an
option:

Dim s As String
s = Join(WorksheetFunction.Transpose(Range(Cells(7, 1),
Cells(Rows.Count, 1).End(xlUp))), ",")
Debug.Print s

--
Dana DeLouis
Win XP & Office 2003


"Adam" wrote in message
...
I got your code to work. Thanks!!

"Don Guillett" wrote:

Fully tested. How did you modify to suit your needs.

--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
This did not seem to work either.

"Don Guillett" wrote:

try
Sub stringem()
For Each C In Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row)
mystring = mystring & C & ","
Next
MsgBox Left(mystring, Len(mystring) - 1)

End Sub

--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
I currently have a spreadsheet that has an infinate number of rows
determined
by the user.

The code states:

For row=7 to Worksheets('Example').UsedRange.Row.Count

String1 = Worksheets("Example").Cells.(Row,1).Value

Endfor

Now I am trying to accomplish it to just add to the string opposed
to
just
creating a new line each time. This information is finally
outputted
to
Word.

For example: What I want
Column

Apples
Oranges
Peaches

to look like: Apples, Oranges, Peaches

What I get:

Apples, Oranges, Peaches
Apples, Oranges
Apples

is the current output. Any suggestions? Thanks

Add to a string to make it continuous
 

Nice. Worked in xl2002 but join is not in xl97

--
Don Guillett
SalesAid Software

"Dana DeLouis" wrote in message
...
The latest versions of Excel might be able to do it this way. Just an
option:

Dim s As String
s = Join(WorksheetFunction.Transpose(Range(Cells(7, 1),
Cells(Rows.Count, 1).End(xlUp))), ",")
Debug.Print s

--
Dana DeLouis
Win XP & Office 2003


"Adam" wrote in message
...
I got your code to work. Thanks!!

"Don Guillett" wrote:

Fully tested. How did you modify to suit your needs.

--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
This did not seem to work either.

"Don Guillett" wrote:

try
Sub stringem()
For Each C In Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row)
mystring = mystring & C & ","
Next
MsgBox Left(mystring, Len(mystring) - 1)

End Sub

--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
I currently have a spreadsheet that has an infinate number of
rows
determined
by the user.

The code states:

For row=7 to Worksheets('Example').UsedRange.Row.Count

String1 = Worksheets("Example").Cells.(Row,1).Value

Endfor

Now I am trying to accomplish it to just add to the string
opposed
to
just
creating a new line each time. This information is finally
outputted
to
Word.

For example: What I want
Column

Apples
Oranges
Peaches

to look like: Apples, Oranges, Peaches

What I get:

Apples, Oranges, Peaches
Apples, Oranges
Apples

is the current output. Any suggestions? Thanks

Add to a string to make it continuous
 

Added in xl2k.

Don Guillett wrote:

Nice. Worked in xl2002 but join is not in xl97

--
Don Guillett
SalesAid Software

"Dana DeLouis" wrote in message
...
The latest versions of Excel might be able to do it this way. Just an
option:

Dim s As String
s = Join(WorksheetFunction.Transpose(Range(Cells(7, 1),
Cells(Rows.Count, 1).End(xlUp))), ",")
Debug.Print s

--
Dana DeLouis
Win XP & Office 2003


"Adam" wrote in message
...
I got your code to work. Thanks!!

"Don Guillett" wrote:

Fully tested. How did you modify to suit your needs.

--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
This did not seem to work either.

"Don Guillett" wrote:

try
Sub stringem()
For Each C In Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row)
mystring = mystring & C & ","
Next
MsgBox Left(mystring, Len(mystring) - 1)

End Sub

--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
I currently have a spreadsheet that has an infinate number of
rows
determined
by the user.

The code states:

For row=7 to Worksheets('Example').UsedRange.Row.Count

String1 = Worksheets("Example").Cells.(Row,1).Value

Endfor

Now I am trying to accomplish it to just add to the string
opposed
to
just
creating a new line each time. This information is finally
outputted
to
Word.

For example: What I want
Column

Apples
Oranges
Peaches

to look like: Apples, Oranges, Peaches

What I get:

Apples, Oranges, Peaches
Apples, Oranges
Apples

is the current output. Any suggestions? Thanks











--

Dave Peterson