我是新来的,在从另一张表填充数据时遇到了问题。
Sheet1 如下所示:
_|___A___|___B___|___C___|
1| 1 |John |1,35,12|
2| 2 |Derek |45,2,1 |
and so....
工作表2
_|___A___|___B___|
1| 1 |Hammer |
2| 2 |Nails |
. . .
12| 12 |Car |
. . .
35| 35 |Rope |
. . .
45| 45 |Vase |
我需要用 Sheet2 中的名称(以“,”或“,”分隔)替换 Sheet1 中的 ID,以获得以下结果:
_|___A___|___B___|_______C_________|
1| 1 |John |Hammer,Rope,Car |
2| 2 |Derek |Vase,Nails,Hammer|
and so....
我不知道这是否可能,但如果可能的话请帮助我
谢谢 :)
答案1
将此代码添加到 Excel 中的新模块中:
Function SplitThenFind(cell As String, sourceColumn As Range)
Dim myArray As Variant
Dim element As Variant
Dim result As String
Dim findResult As Range
myArray = Split(cell, ",")
For Each element In myArray
Set findResult = Application.Worksheets(sourceColumn.Worksheet.Index).Range(sourceColumn.Address).Find(element, Lookat:=xlWhole)
If Not (findResult Is Nothing) Then
' with offset you can select the column you need
result = result & findResult.Cells.Offset(0, 1).Value & ","
End If
Next
If Len(result) > 0 Then
result = Left(result, Len(result) - 1)
End If
SplitThenFind = result
End Function