跨表引用对应替换求助

szqhb

跨表引用对应替换求助.zip (25.78 KB, 下载次数: 4)

2020-1-12 17:58 上传

点击文件名下载附件

问题详见截图右述

iwangyi

Sub CC()
For Each Rng In Range("C2:C" & [C65536].End(xlUp).Row)
Set C = Sheets("班级信息").Columns(1).Find(Rng, , , 1)
If Not C Is Nothing Then Rng.Resize(, 4) = C.Resize(, 4).Value
Next
End Sub

iwangyi

附件参考*****************

bobofun

一对一 条件查找直接vlokup 更快

excel海

Sub 班级信息录入()
    Dim n%, i%, arr, j%
    Dim dic
    Set dic = CreateObject("scripting.dictionary")
    arr = Sheet3.UsedRange
    For i = 2 To UBound(arr)
        dic(arr(i, 1)) = i
    Next
    n = Sheet1.Cells(Rows.Count, 3).End(xlUp).Row
    ReDim brr(1 To n - 1, 1 To 3)
    For i = 1 To n - 1
        For j = 1 To 2
            brr(i, j) = arr(dic(Sheet1.Cells(i + 1, 3).Value), j + 1)
        Next
        brr(i, 3) = arr(dic(Sheet1.Cells(i + 1, 3).Value), 5)
    Next
    Sheet1.Range("D2:F2").Resize(UBound(brr)).ClearContents
    Sheet1.Range("D2:F2").Resize(UBound(brr)) = brr
End Sub

szqhb

iwangyi 发表于 2020-1-12 18:25
附件参考*****************

谢谢您。若是连续的区域，这个代码好简短！

szqhb

excel海 发表于 2020-1-12 23:47
Sub 班级信息录入()
    Dim n%, i%, arr, j%
    Dim dic

非常感谢！