时间戳

首页 > 解决方案 > 查找特定工作表

excel - 查找特定工作表

问题描述

我从不同的工作表填充工作表的 A 列数据。

在其特定工作表中引用该特定数据的 vlookup 不起作用，并且 Excel 正在弹出一个窗口以选择工作表。

我的部分代码如下：

Dim i As Integer
Dim fdof As Date
fdof = Date - Day(Date) + 1
j = 2
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "*2018" Or ws.Name Like "*2019" Then

        For i = 2 To ws.Range("A1").SpecialCells(xlLastCell).Row

           If Evaluate("OR(ISNUMBER(MATCH({""*-*""},{""" & ws.Cells(i, 1).Value & """},0)))") And ws.Cells(i, 5).Value = "Vacant" And ws.Cells(i, 3).Value >= fdof Then

                Sheets("Rapport de Disponibilité").Cells(j, 1) = ws.Cells(i, 1)
                Sheets("Rapport de Disponibilité").Cells(j, 2).Formula = "=IFERROR(VLookup($A" & j & ",ws!$A:$T,3,FALSE),"""")"
'                Sheets("Rapport de Disponibilité").Cells(j, 3).Formula = "=IFERROR(VLookup($A" & j & ",ws!$A:$T,4,FALSE),"""")"
'                Sheets("Rapport de Disponibilité").Cells(j, 4).Formula = "=IFERROR(VLookup($A" & j & ",ws!$A:$T,8,FALSE),"""")"
'                Sheets("Rapport de Disponibilité").Cells(j, 5).Formula = "=IFERROR(VLookup($A" & j & ",ws!$A:$T,15,FALSE),"""")"
'                Sheets("Rapport de Disponibilité").Cells(j, 6).Formula = "=IFERROR(VLookup($A" & j & ",ws!$A:$T,16,FALSE),"""")"
'                Sheets("Rapport de Disponibilité").Cells(j, 7).Formula = "=IFERROR(VLookup($A" & j & ",ws!$A:$T,20,FALSE),"""")"

                j = j + 1
            End If

        Next i
    End If
Next ws

我相信错误在 ws 的这一行！

Sheets("Rapport de Disponibilité").Cells(j, 2).Formula = "=IFERROR(VLookup($A" & j & ",ws!$A:$T,3,FALSE),"""")"

标签： excelvbavlookupworksheet-function

解决方案

尝试，

Sheets("Rapport de Disponibilité").Cells(j, 2).Formula = "=IFERROR(VLookup($A" & j & ", '" & ws.name & "'!$A:$T, 3, FALSE), text(,))"
'alternate
Sheets("Rapport de Disponibilité").Cells(j, 2).Formula = "=IFERROR(VLookup($A" & j & ", " & ws.range("A:T").address(0, 0, external:=true) & ", 3, FALSE), text(,))"

您的 EVALUATE 的逻辑并没有真正的意义。FIND 会比 MATCH 更好，我没有看到 OR 的第二条语句。

...
If cbool(instr(1, ws.Cells(i, 1).Value, "-")) And ws.Cells(i, 5).Value = "Vacant" And ws.Cells(i, 3).Value >= fdof Then
...

在 With ... End With 块中，您的公式分配将更有效且更具可读性。

...
with workSheets("Rapport de Disponibilité")
    .Cells(j, 1) = ws.Cells(i, 1)
    .Cells(j, 2).Formula = "=IFERROR(VLookup($A" & j & ", " & ws.range("A:T").address(0, 0, external:=true) & ", 3, FALSE), text(,))"
    .Cells(j, 3).Formula = "=IFERROR(VLookup($A" & j & ", " & ws.range("A:T").address(0, 0, external:=true) & ", 4, FALSE), text(,))"
    .Cells(j, 4).Formula = "=IFERROR(VLookup($A" & j & ", " & ws.range("A:T").address(0, 0, external:=true) & ", 8, FALSE), text(,))"
    .Cells(j, 5).Formula = "=IFERROR(VLookup($A" & j & ", " & ws.range("A:T").address(0, 0, external:=true) & ", 15, FALSE), text(,))"
    .Cells(j, 6).Formula = "=IFERROR(VLookup($A" & j & ", " & ws.range("A:T").address(0, 0, external:=true) & ", 16, FALSE), text(,))"
    .Cells(j, 7).Formula = "=IFERROR(VLookup($A" & j & ", " & ws.range("A:T").address(0, 0, external:=true) & ", 20, FALSE), text(,))"
end with
...

推荐阅读