CODEWITHSUNDEEP
vbaexcelloopsfor-loop
decemberrobot
decemberrobot

Reputation: 531

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

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?

Upvotes: 1

Views: 17832

Answers (3)

Nathan_Sav
Nathan_Sav

Reputation: 8531

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

Upvotes: 1

Variatus
Variatus

Reputation: 14373

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).

Upvotes: 0

YowE3K
YowE3K

Reputation: 23974

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

Upvotes: 2

Related Questions