Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
make continuous list of days of week without Sunday | Excel Discussion (Misc queries) | |||
how do i make a continuous data table | Excel Discussion (Misc queries) | |||
How do I make letters not show up in a alpha-numeric string? | Excel Worksheet Functions | |||
How do I make this string bold? | Excel Programming | |||
make string variable reference VBA sheet name | Excel Programming |