+ Reply to Thread
Results 1 to 4 of 4

VB code to concatenate 100+ cells

  1. #1
    LJones
    Guest

    VB code to concatenate 100+ cells

    Hello,

    Very amateur VB user, need help!

    I need to write VB code to concatenate the data in 100 or more cells into a
    single string. This is easy for a small number of cells:

    ActiveCell.Offset(0, 0).FormulaR1C1 = _
    ActiveCell.Offset(0, -1) & "," & ActiveCell.Offset(0, -2) etc.

    But how do I do this without needing to type the "ActiveCell.Offset" 100
    times? Is there any way to included a for/next loop within the concatenation?

    Thanks!
    /LJ

  2. #2
    Toppers
    Guest

    RE: VB code to concatenate 100+ cells

    Hi,


    Dim myString as string

    myString=""

    For r=1 to 100
    myString=MyString & cells(r,1) <=== Rows 1 to 100 of column A
    next r


    "LJones" wrote:

    > Hello,
    >
    > Very amateur VB user, need help!
    >
    > I need to write VB code to concatenate the data in 100 or more cells into a
    > single string. This is easy for a small number of cells:
    >
    > ActiveCell.Offset(0, 0).FormulaR1C1 = _
    > ActiveCell.Offset(0, -1) & "," & ActiveCell.Offset(0, -2) etc.
    >
    > But how do I do this without needing to type the "ActiveCell.Offset" 100
    > times? Is there any way to included a for/next loop within the concatenation?
    >
    > Thanks!
    > /LJ


  3. #3
    Toppers
    Guest

    RE: VB code to concatenate 100+ cells

    Soory ... forgot about comma:

    Dim myString as string

    myString=""

    For r=1 to 100
    myString=MyString & cells(r,1) & "," <=== Rows 1 to 100 of column A
    next r

    myString=left(myString,len(mystring)-1)


    "Toppers" wrote:

    > Hi,
    >
    >
    > Dim myString as string
    >
    > myString=""
    >
    > For r=1 to 100
    > myString=MyString & cells(r,1) <=== Rows 1 to 100 of column A
    > next r
    >
    >
    > "LJones" wrote:
    >
    > > Hello,
    > >
    > > Very amateur VB user, need help!
    > >
    > > I need to write VB code to concatenate the data in 100 or more cells into a
    > > single string. This is easy for a small number of cells:
    > >
    > > ActiveCell.Offset(0, 0).FormulaR1C1 = _
    > > ActiveCell.Offset(0, -1) & "," & ActiveCell.Offset(0, -2) etc.
    > >
    > > But how do I do this without needing to type the "ActiveCell.Offset" 100
    > > times? Is there any way to included a for/next loop within the concatenation?
    > >
    > > Thanks!
    > > /LJ


  4. #4
    LJones
    Guest

    RE: VB code to concatenate 100+ cells

    Perfect! Thx again,
    LJ

    "Toppers" wrote:

    > Soory ... forgot about comma:
    >
    > Dim myString as string
    >
    > myString=""
    >
    > For r=1 to 100
    > myString=MyString & cells(r,1) & "," <=== Rows 1 to 100 of column A
    > next r
    >
    > myString=left(myString,len(mystring)-1)
    >
    >
    > "Toppers" wrote:
    >
    > > Hi,
    > >
    > >
    > > Dim myString as string
    > >
    > > myString=""
    > >
    > > For r=1 to 100
    > > myString=MyString & cells(r,1) <=== Rows 1 to 100 of column A
    > > next r
    > >
    > >
    > > "LJones" wrote:
    > >
    > > > Hello,
    > > >
    > > > Very amateur VB user, need help!
    > > >
    > > > I need to write VB code to concatenate the data in 100 or more cells into a
    > > > single string. This is easy for a small number of cells:
    > > >
    > > > ActiveCell.Offset(0, 0).FormulaR1C1 = _
    > > > ActiveCell.Offset(0, -1) & "," & ActiveCell.Offset(0, -2) etc.
    > > >
    > > > But how do I do this without needing to type the "ActiveCell.Offset" 100
    > > > times? Is there any way to included a for/next loop within the concatenation?
    > > >
    > > > Thanks!
    > > > /LJ


+ 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