How to concatenate strings in array using for loop in excel vba

Question

I am currently trying to iterate through rows in an excel sheet and concatenate the columns with some other hard coded data. Right now I am doing it the manual way as I do not know how to concatenate using excel VBA.

My code looks like this right now:

 myproperty =          Chr(34) & Worksheets(tbValue).Cells(6, 15).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 15).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 16).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 16).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 17).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 17).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 18).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 18).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 19).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 19).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 20).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 20).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 21).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 21).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 22).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 22).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 23).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 23).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 24).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 24).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 25).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 25).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 26).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 26).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 27).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 27).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 28).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 28).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 29).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 29).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 30).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 30).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 31).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 31).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 32).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 32).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 33).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 33).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 34).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 34).Value & Chr(34) & ";" & _
                       Chr(34) & Worksheets(tbValue).Cells(6, 35).Value & Chr(34) & ":" & Chr(34) & Worksheets(tbValue).Cells(rrow, 35).Value & Chr(34) & ";"

Right now it is all populated through the hard way, and I want to do this using a for loop but I have no idea how to concatenate using excel vba.

I have now a for loop that loops through all the rows, "rrow" is the iterator for the row.

Anyone knows how to concatenate all these with a for-loop?

YowE3K · Accepted Answer · 2017-04-25 09:49:14Z

2

The loop you are looking for is probably something like:

Dim c As Long
myproperty = ""
For c = 15 To 35
    myproperty = myproperty & _
                 Chr(34) & Worksheets(tbValue).Cells(6, c).Value & Chr(34) & _
                 ":" & _
                 Chr(34) & Worksheets(tbValue).Cells(rrow, c).Value & Chr(34) & _
                 ";"
Next

answered Apr 25, 2017 at 9:49

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

decemberrobot Over a year ago

thanks, I managed to figure it out. It is something like yours.

Nathan_Sav · Accepted Answer · 2017-04-25 10:36:02Z

1

Something like this

Sub x()

Dim a() As Variant

a = worksheets("Sheet1").Range("a1:a8").Value
a = Application.Transpose(a)

Debug.Print "'" & Join(a, "':'") & "'"

End Sub

edited Apr 25, 2017 at 10:36

answered Apr 25, 2017 at 9:36

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

3 Comments

Pᴇʜ Over a year ago

According to VBA Best Practices you should use a full qualified range like a = Worksheets("Sheet1").Range("A1:A8").Value

h2so4 Over a year ago

with double quote Debug.Print """" & Join(a, """:""") & """"

brettdj Over a year ago

Better as it avoids a range loop.

Variatus · Accepted Answer · 2017-04-25 10:23:39Z

I guess this is, more or less, what you are looking for.

Private Sub ConcatValues()

    Dim MyProperty As String
    Dim Fun() As String
    Dim i As Integer
    Dim Rl As Long
    Dim R As Long, C As Long

    i = -1
    With Worksheets("tbValue")
        ' find the last used row in column A
        Rl = .Cells(.Rows.Count, "A").End(xlUp).Row

        For R = 7 To Rl             ' start with row 7 (??)
            i = i + 1
            ReDim Preserve Fun(i)
            For C = 16 To 35
                Fun(i) = Fun(i) & Chr(34) & .Cells(6, C).Value & Chr(34) & _
                         ":" & Chr(34) & .Cells(R, C).Value & Chr(34) & "; "
            Next C
            MyProperty = Join(Fun)
            Debug.Print MyProperty
        Next R
    End With
End Sub

I don't know what you want to do with the concatenated string. So the above code just outputs it to the Immediate Window. You might assign it to a cell or output it to a text file.

NB. I tried to use the semicolon as a delimiter of the Join function. VBA refused to accept any delimiter, however, perhaps because of the final quotation mark Chr(34).

Collectives™ on Stack Overflow

How to concatenate strings in array using for loop in excel vba

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related