excel - 来自不同工作表的 Vlookup 动态范围引用

Question

我在一系列单元格中插入一个 vlookup 公式。然而，该公式中有一个动态引用，我很难正确构建它。

这是我的代码:

Sheets("Count").Activate

Dim myValue As Date
myValue = InputBox("Please enter the date you want to update")
Sheets("Count").Range("A1").Value = myValue

Dim ra As Range, raEnd As Range
Dim date1 As Date
date1 = Range("A1")

Set ra = Cells.Find(What:=date1 _
                    , LookIn:=xlFormulas _
                    , LookAt:=xlPart _
                    , SearchOrder:=xlByRows _
                    , SearchDirection:=xlNext _
                    , MatchCase:=False _
                    , SearchFormat:=False)

    If ra Is Nothing Then
    MsgBox ("Date not found, check the format.")

    Else

        Dim Lastrow As Long, rng As Range
        Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
        Set rng = Range(ra.Offset(1, 0), Cells(Lastrow, ra.Column))

        Dim endrow As Long, i As Long, lcol As Integer, startcells As Range
        lcol = Sheets("Count").Range("B1").Value
        endrow = Sheets("Input").Cells(Rows.Count, lcol).End(xlUp).Row
        i = 6
        Set startcells = Sheets("Input").Cells(i, lcol)

        Sheets("Input").Activate

        Dim xrnge As Range, s
        Set xrnge = Range(startcells, Cells(endrow, lcol))
        s = xrnge.Address

        Sheets("Count").Activate
        rng.Formula = "=IFERROR(VLOOKUP($B8,'" & "input" & "'!s,1,0),"""")"

    End If

End Sub

我无法工作的部分是

rng.Formula = "=IFERROR(VLOOKUP($B8,'" & "input" & "'!s,1,0),"""")"

我想我快到了，因为在我要插入公式的范围内，如下所示:

=IFERROR(VLOOKUP($B20;Input!s;1;0);"") 

你能帮我弄清楚为什么它显示 s 而不是范围本身吗？

Answer 1

如果我理解正确你想要 s保存范围地址和input作为静态工作表引用。在这种情况下，您应该这样编写公式:

rng.Formula = "=IFERROR(VLOOKUP($B8,input!" & s & ",1,0),"""")"

说 s保存范围 S3:S50 的地址单元格中的结果将是:

=IFERROR(VLOOKUP($B8,Input!$S$3:$S$50,1,0),"")

正如预期的那样，这将是一个有效的公式。