+ Reply to Thread
Results 1 to 12 of 12

Add to a string to make it continuous

  1. #1
    Adam
    Guest

    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. #2
    Chip Pearson
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    >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. #3
    Don Guillett
    Guest

    Re: 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
    [email protected]
    "Adam" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #4
    Adam
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > >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. #5
    Adam
    Guest

    Re: 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
    > [email protected]
    > "Adam" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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
    > >
    > >

    >
    >
    >


  6. #6
    Don Guillett
    Guest

    Re: Add to a string to make it continuous

    Did you try my suggestion?

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "Adam" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > >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. #7
    Don Guillett
    Guest

    Re: Add to a string to make it continuous

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

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "Adam" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > > [email protected]
    > > "Adam" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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. #8
    Tom Ogilvy
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > > [email protected]
    > > "Adam" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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. #9
    Adam
    Guest

    Re: 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
    > [email protected]
    > "Adam" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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
    > > > [email protected]
    > > > "Adam" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > 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. #10
    Dana DeLouis
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    >I got your code to work. Thanks!!
    >
    > "Don Guillett" wrote:
    >
    >> Fully tested. How did you modify to suit your needs.
    >>
    >> --
    >> Don Guillett
    >> SalesAid Software
    >> [email protected]
    >> "Adam" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > 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
    >> > > [email protected]
    >> > > "Adam" <[email protected]> wrote in message
    >> > > news:[email protected]...
    >> > > > 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. #11
    Don Guillett
    Guest

    Re: Add to a string to make it continuous

    Nice. Worked in xl2002 but join is not in xl97

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "Dana DeLouis" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > >I got your code to work. Thanks!!
    > >
    > > "Don Guillett" wrote:
    > >
    > >> Fully tested. How did you modify to suit your needs.
    > >>
    > >> --
    > >> Don Guillett
    > >> SalesAid Software
    > >> [email protected]
    > >> "Adam" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > 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
    > >> > > [email protected]
    > >> > > "Adam" <[email protected]> wrote in message
    > >> > > news:[email protected]...
    > >> > > > 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. #12
    Dave Peterson
    Guest

    Re: 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
    > [email protected]
    > "Dana DeLouis" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > >I got your code to work. Thanks!!
    > > >
    > > > "Don Guillett" wrote:
    > > >
    > > >> Fully tested. How did you modify to suit your needs.
    > > >>
    > > >> --
    > > >> Don Guillett
    > > >> SalesAid Software
    > > >> [email protected]
    > > >> "Adam" <[email protected]> wrote in message
    > > >> news:[email protected]...
    > > >> > 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
    > > >> > > [email protected]
    > > >> > > "Adam" <[email protected]> wrote in message
    > > >> > > news:[email protected]...
    > > >> > > > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1