|
求助如何从数据源中工作表查找出汇总工作表中相应学号的人名及各科成绩。
以下是我参考别人写的,不知道啥问题,运行出错。
Sub multiVlookup()
Dim myDic As Object, i As Integer, sht1 As Worksheet, maxRow As Integer, totalCnt As Integer, values As String, sht2 As Worksheet
Application.ScreenUpdating = False
Set myDic = CreateObject("scripting.dictionary")
Set sht1 = ThisWorkbook.Sheets("数据源")
Set sht2 = ThisWorkbook.Sheets("汇总")
maxRow = sht1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To maxRow
values = sht1.Cells(i, 2).Value & "_" & sht1.Cells(i, 3).Value & "_" & sht1.Cells(i, 4).Value & "_" & sht1.Cells(i, 5).Value '此处以"_"作为拼接字符,如果您觉得该字符可能出现在value中,可以换其它非常用字符来代替
If myDic.Exists(sht1.Cells(i, 1).Value) = False Then
myDic.Add sht.Cells(i, 1).Value, values
End If
Next
maxRow = sht2.Cells(Rows.Count, 1).End(xlUp).Row '读取第五列的最后一行行号
For i = 2 To maxRow
values = myDic.Item(sht2.Cells(i, 1).Value) '根据第一列的key,将对应的item写入第六列
sht2.Cells(i, 2).Value = Split(values, "_")(0) '存入split分段后的数组的第一个元素
sht2.Cells(i, 3).Value = Split(values, "_")(1) '存入split分段后的数组的第二个元素
sht2.Cells(i, 4).Value = Split(values, "_")(2)
sht2.Cells(i, 5).Value = Split(values, "_")(3)
Next
Application.ScreenUpdating = True
End Sub
本帖最后由 哥儿- 于 2023-1-4 22:00 编辑
判断一下就OK
For i = 2 To maxRow
if myDic.exists(sht2.Cells(i, 1).Value) then
values = myDic.Item(sht2.Cells(i, 1).Value) '根据第一列的key,将对应的item写入第六列
sht2.Cells(i, 2).Value = Split(values, "_")(0) '存入split分段后的数组的第一个元素
sht2.Cells(i, 3).Value = Split(values, "_")(1) '存入split分段后的数组的第二个元素
sht2.Cells(i, 4).Value = Split(values, "_")(2)
sht2.Cells(i, 5).Value = Split(values, "_")(3)
end if
Next
|
|