A表需要从多个B表中的多个sheet中查找 A表在的每一行数据并更新。

[wwle]

我有一个A表



这是A表图示，成绩列是空白的。

成绩在一堆散乱的B表中 ，B表有多个，每个B 表有多个sheet




我需要从多个B表中的多个sheet中查找 A表在的每一行数据并把找到的成绩数据带回到A 表中。





我在网上搜了一段代码，意思差别有点大，贴过来，你们可以改改。我不会VBA ,

Sub text()
    Dim myfile$, myname$, wb As Workbook, rng As Range, arr, brr(), c, i%, t
    arr = Array("银行存款", "清算备付金", "存出保证金", "股票投资")
    ReDim brr(1 To 1000, 1 To 3)
    Application.ScreenUpdating = False
    t = Timer
    myfile = ThisWorkbook.Path & ""
    myname = Dir(myfile & "*.xls")
    Cells.Clear
    Range("A1:C1") = Array("工作簿名称", "科目名称", "市值")
    Do While myname <> ""
        If myname <> ThisWorkbook.Name Then
            Set wb = GetObject(myfile & myname)
            With wb.Sheets(1)
                For Each c In arr
                    Set rng = .UsedRange.Find(c, lookat:=xlWhole)
                    If Not rng Is Nothing Then
                        i = i + 1
                        brr(i, 1) = wb.Name
                        brr(i, 2) = c
                        brr(i, 3) = rng.Offset(0, 6)
                    End If
                 Next
             End With
             wb.Close False
          End If
          If i <> 0 Then i = i + 1
          myname = Dir
      Loop
      Range("A2").Resize(i, 3) = brr
      Columns("A:C").AutoFit
      Columns(3).NumberFormatLocal = "￥#,##0.00;￥-#,##0.00"
      Application.ScreenUpdating = True
      MsgBox "汇总完毕，用时: " & Format(Timer - t, "0.00") & " 秒!"
End Sub