vba 如何在excel vba中使用for循环连接数组中的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43606537/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to concatenate strings in array using for loop in excel vba
提问by decemberrobot
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.
我目前正在尝试遍历 Excel 工作表中的行并将列与其他一些硬编码数据连接起来。现在我正在以手动方式进行操作，因为我不知道如何使用 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.
现在它都是通过艰难的方式填充的，我想使用 for 循环来做到这一点，但我不知道如何使用 excel vba 进行连接。
I have now a for loop that loops through all the rows, "rrow" is the iterator for the row.
我现在有一个循环遍历所有行的 for 循环，“rrow”是该行的迭代器。
Anyone knows how to concatenate all these with a for-loop?
任何人都知道如何将所有这些与 for 循环连接起来？
采纳答案by YowE3K
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
回答by Nathan_Sav
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
回答by Variatus
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).
注意。我尝试使用分号作为 Join 函数的分隔符。然而，VBA 拒绝接受任何分隔符，可能是因为最后的引号 Chr(34)。
