VBA中使用index+match

舟心ty

已于 2022-10-27 22:11:20 修改

阅读量906

点赞数 1

分类专栏： VBA 文章标签：开发语言

于 2022-10-23 21:23:48 首次发布

本文链接：https://blog.csdn.net/sx01117/article/details/127481128

版权

VBA 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

Sub test()
Dim Arr, Brr, arr1, arr2, brr1, brr2
Dim i, j, k
Dim hang, lie
 
Arr = Range("A50:E54")
arr1 = Range("A50:A54")
arr1 = Application.WorksheetFunction.Transpose(arr1)
arr2 = Range("A50:E50")
arr2 = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(arr2))
brr1 = Range("I50:I51")
brr1 = Application.WorksheetFunction.Transpose(brr1)
brr2 = Range("J50:J52")
brr2 = Application.WorksheetFunction.Transpose(brr2)
ReDim Brr(1 To UBound(brr1) + 1, 1 To UBound(brr2) + 1)
Brr(1, 1) = "姓名"
For i = 1 To UBound(brr1)
    Brr(i + 1, 1) = brr1(i)
Next i
For j = 1 To UBound(brr2)
    Brr(1, j + 1) = brr2(j)
Next j
 
For i = 2 To UBound(Brr)
    hang = Application.WorksheetFunction.Match(Brr(i, 1), arr1, 0)
    For j = 2 To UBound(Brr, 2)
        lie = Application.WorksheetFunction.Match(Brr(1, j), arr2, 0)
        Brr(i, j) = Application.WorksheetFunction.Index(Arr, hang, lie)
    Next j
Next i
Range("A58").Resize(UBound(Brr), UBound(Brr, 2)) = Brr
 
End Sub