Excel Hints

Results 1 to 3 of 3

Thread: Solved: Build Dynamic String into Cell

  1. #1

    Solved: Build Dynamic String into Cell

    Hi,

    I am trying to go through a list of items and have them be stored in a dynamic array. Then that list would look like 'A, B, C, D'... in a specified cell. I'm not sure how to make a string 'dynamic'.

    I have this code so far for 20 at a time:

    VB:
    Dim arr(1 To 20) As Variant, rng As Range, id As String, rng2 As Range 
     
    Range("b2").Activate 
     
    Do Until IsEmpty(ActiveCell) 
         
         
        For i = 1 To 20 '20
            If i = 1 Then 
                Set rng = ActiveCell.Offset(0, 1) 
            End If 
            arr(i) = ActiveCell.Value 
             
            ActiveCell.Offset(1, 0).Activate 
        Next i 
        Set rng2 = ActiveCell 
         
        id = arr(1) + ", " + arr(2) + ", " + arr(3) + ", " + arr(4) + ",  " + arr(5) + ", " + arr(6) + ", " + arr(7) + ", " + arr(8) + ", " +  arr(9) + ", " + arr(10) + ", " + arr(11) + ", " + arr(12) + ", " +  arr(13) + ", " + arr(14) + ", " + arr(15) + ", " + arr(16) + ", " +  arr(17) + ", " + arr(18) + ", " + arr(19) + ", " + arr(20) 
         
        rng.Value = id 
         
         
    Loop 
    
    
    Formatting tags added by mark007
    Clearly, this hardcode is not adaptable, so how could this to a nonspecified number?

    Thank you for your input!

  2. #2
    Look at Join().

  3. #3
    Thanks! Figured it out.

Posting Permissions

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