时间戳

首页 > 解决方案 > 与不同工作簿中的变量电子表格名称相比，循环遍历多个电子表格上的 VLOOKUP 公式

excel - 与不同工作簿中的变量电子表格名称相比，循环遍历多个电子表格上的 VLOOKUP 公式

问题描述

我有两个不同的工作坊。工作簿 A 和工作簿 B。我想应用 vlookup 来查看两个工作簿上名为“1”的工作表上的工作簿 A 和工作簿 B 之间的匹配值。然后我想循环并对名为“2”的工作表执行相同的操作，依此类推。我的宏不太成功，它半有效。注意一些工作表不存在于工作簿 B 但存在于工作簿 A任何人都知道如何实现这一点。我怀疑这与 w 变量有关。有没有办法计算一个字符串？

Sub y()
Dim sh As Worksheet
Dim wb As Workbook
     
For Each sh In ActiveWorkbook.Sheets
    sh.Activate
    LR = Range("A" & Rows.Count).End(xlUp).Row
    For i = 31 To LR
        For w = 1 To 41
            On Error Resume Next
            Cells(i, "E").Value = Application.WorksheetFunction.VLookup(Cells(i, "A"), Workbooks("WorkbookB.xlsx").Worksheets(w).Range("A31:E1000"), 5, 0)
        Next w
    Next i
Next sh
End Sub

标签： excelvbaloopsvlookup

解决方案

要“查看匹配的值”，请使用Match

Option Explicit
Sub y()
   
    Dim wbA As Workbook, wbB As Workbook
    Dim wsA As Worksheet, wsB As Worksheet
    Dim LR As Long, i As Long, rng As Range, idx As Variant
         
    Set wbA = ActiveWorkbook
    Set wbB = Workbooks("WorkbookB.xlsx")

    For Each wsA In wbA.Sheets
        
        On Error Resume Next
        Set wsB = wbB.Sheets(wsA.Name)
        On Error GoTo 0

        If Not wsB Is Nothing Then
            ' search range
            LR = wsB.Cells(Rows.Count, "A").End(xlUp).Row
            Set rng = wsB.Range("A31:A" & LR)

            LR = wsA.Cells(Rows.Count, "A").End(xlUp).Row
            For i = 31 To LR
                idx = Application.Match(wsA.Cells(i, "A"), rng, 0)
                If IsError(idx) Then
                   ' do nothing
                Else
                    wsA.Cells(i, "E") = rng.Cells(idx, "E") ' column E
                End If
            Next
        End If

    Next
    MsgBox "Done"
End Sub

推荐阅读