vba 如何在Excel的单个单元格中显示多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6156537/
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 display multiple values in a single cell in Excel
提问by RockOrDie357
I'm am relatively familiar with Excel and its functions, but am very new to VBA (I do however have background in MATLAB and C). Basically what I have is a sheet with a different machine populating each column header and a name of an employee populating the first column. The table contains text values of either "Train", indicating that the person in that row is trained on the equipment in the specified column, or "No", indicating that they are not. What I want to do is to make a separate sheet that has the Equipment in the first column and one column headered as "Trained". Each cell will theoretically be populated with the names of the people who are trained on the equipment for that row. I have a for loop code in VBA that successfully outputs the names into the immediate window
我对 Excel 及其函数比较熟悉，但对 VBA 非常陌生（但是我确实有 MATLAB 和 C 方面的背景）。基本上我拥有的是一张工作表，其中有一台不同的机器填充每个列标题，以及填充第一列的员工姓名。该表包含文本值“Train”（表示该行中的人员接受过指定列中设备的培训）或“No”（表示未接受）的文本值。我想要做的是制作一个单独的工作表，其中第一列中有设备，一列标题为“已培训”。理论上，每个单元格都填充有接受过该行设备培训的人员的姓名。我在 VBA 中有一个 for 循环代码，它成功地将名称输出到即时窗口中
Function Train(Data As Range, Name As Range)
For Counter = 1 To Data.Rows.Count
If Data(Counter, 1).Value = "Train" Then
'Debug.Print Name(Counter, 1)
End If
Next Counter
End Function
but I have been unable in a few hours of searching to figure out how to display these values in a single cell. Is this possible?
但是经过几个小时的搜索，我一直无法弄清楚如何在单个单元格中显示这些值。这可能吗？
Thanks in advance!
提前致谢！
采纳答案by jonsca
Use the concatenation operator (&) to assemble the values into a string:
使用连接运算符 ( &) 将值组合成字符串：
Dim names as String
names = ""
For Counter = 1 To Data.Rows.Count
If Data(Counter, 1).Value = "Train" Then
If counter = 1 Then
names = names & Name(counter, 1)
Else
names = names & "," & Name(counter, 1)
End If
End If
Next Counter
Then just place names in whatever cell you want.
然后只需将名称放在您想要的任何单元格中。
回答by Tipx
You have to choose if you want to do "For each person, for each machine", or "for each machine, for each person" first. Let say you want to go with the second idea, you could go with this pseudo code:
您必须先选择是“为每个人，为每台机器”，还是“为每台机器，为每个人”。假设您想采用第二个想法，可以使用以下伪代码：
set wsEmployee = Worksheets("EmployeeSheet")
set wsEmployee = Worksheets("MachineSheet")
'Clear MachineSheet and add headers here
xEmployee = 2
yMachine = 2
do while (wsEmployee.Cells(1, xEmployee).Value <> "") 'or your loop way here
yEmployee = 2
trained = ""
do while (wsEmployee.Cells(yEmployee, 1).Value <> "") 'or your loop way here
if (wsEmployee.Cells(yEmployee, xEmployee).Value = "Trained") then
trained = trained & wsEmployee.Cells(yEmployee, 1).Value & ", "
end if
yEmployee = yEmployee + 1
loop
'remove the last , in the trained string
wsMachine.Cells(yMachine, 1).Value = wsEmployee.Cells(1, xEmployee).Value
wsMachine.Cells(yMachine, 2).Value = trained
yMachine = yMachine + 1
xEmployee = xEmployee + 1
loop
That's the basic idea. For better performances, I would do all these operation in some arrays and paste them in one operation.
这就是基本的想法。为了获得更好的性能，我会在一些数组中执行所有这些操作并将它们粘贴到一个操作中。
