带范围的宏中的 Excel Vlookup

发布于 2024-10-09 01:54:34 字数 659 浏览 3 评论 0原文

我正在尝试向 Excel 宏中的单元格添加 vlookup，但是我似乎无法正确获取函数的参数，也无法获取范围函数的参数

请帮助我说明如何编写代码的最后一行：

            RangeStartRow = 2
            RangeStartColumn = 1 + 11 + (3 * (AverageSheetIndex - RowCounter - 1))
            RangeEndRow = LastCol
            RangeEndColumn = 2 + 11 + (3 * (AverageSheetIndex - RowCounter - 1))
            ActiveCell.Formula = WorksheetFunction.VLookup(ActiveWorkSheet.Cells(1, c), ActiveSheet.range(Cells(RangeStartRow, RangeStartColumn), Cells(RangeEndRow, RangeEndColumn)), 2, False)

我相信代码是直接的（不用介意前 4 行的值），我希望对活动单元格进行 vlookup 以在上面声明的 4 个值的范围内查找单元格（1，c）。

请让我知道如何重写代码的最后一行。

提前致谢。

原文

I'm trying to add a vlookup to a cell in an excel macro, however I can't seem to get the arguments of the function right nor the arguments of the range function

Please help me in stating how to write last line of my code:

            RangeStartRow = 2
            RangeStartColumn = 1 + 11 + (3 * (AverageSheetIndex - RowCounter - 1))
            RangeEndRow = LastCol
            RangeEndColumn = 2 + 11 + (3 * (AverageSheetIndex - RowCounter - 1))
            ActiveCell.Formula = WorksheetFunction.VLookup(ActiveWorkSheet.Cells(1, c), ActiveSheet.range(Cells(RangeStartRow, RangeStartColumn), Cells(RangeEndRow, RangeEndColumn)), 2, False)

I believe the code is straight (never mind the values of the first 4 lines), i wish to do a vlookup on the active cell to look for cell(1,c) in the range of the 4 values declared above.

Please let me know how to rewrite the last line of my code.

Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

第几種人 2024-10-16 01:54:34

ActiveCell.Formula 需要一个字符串来表示您希望在单元格中使用的公式。

 ActiveCell.Formula = "=vlookup(" & activecell.offset(0,1).address & ....

我通常首先在工作表中手动创建公式，然后在“调试”窗口中键入（对于 C3 中的公式），

? range("C3").formulaR1C1

然后根据需要编辑公式并将其复制到代码中。使用 FormulaR1C1 避免检索字母：对于 ActiveCell 为 $C$4，

"=D4" is equivalent to "=RC[1]"
"=$D$4" is equivalent to "=R4C4"

如果您确实想检索 MyCol 的列字母，您可以使用：

split(cells(1,MyCol).address,"$")(1)

为了帮助您重新编写公式，我有时使用以下子程序，从以下位置调用调试窗口：

Sub RngToVba(src As Range)
'Helps to write VBA code that will reproduce formula from existing sheet.
'writes (to the debug window) the VBA code to re-create the formulae in given range
'by Patrick Honorez - www.idevlop.com
'usage: from debug window, type RngToVba [L14:R14]
'                            or RngToVba range("L14:R14")
'then copy generated code to your module
    Dim c As Range
    For Each c In src
        Debug.Print "range(""" & c.Address & """).formula = """ & _
                    Replace(c.Formula, """", """""") & """"""
    Next c
End Sub

ActiveCell.Formula requires a string representing the formula you want to have in the cell.

 ActiveCell.Formula = "=vlookup(" & activecell.offset(0,1).address & ....

I generally start by creating the formula manually in the sheet, then in the Debug window I type (for a formula in C3)

? range("C3").formulaR1C1

Then I edit the formula if required and I copy that in my code. Using formulaR1C1 avoid retrieving the letter: for an ActiveCell beeing $C$4,

"=D4" is equivalent to "=RC[1]"
"=$D$4" is equivalent to "=R4C4"

I f you really want to retrieve the column letter of MyCol, you can use:

split(cells(1,MyCol).address,"$")(1)

To help you rework your formula, I sometimes use the following sub, to be called from debug window:

Sub RngToVba(src As Range)
'Helps to write VBA code that will reproduce formula from existing sheet.
'writes (to the debug window) the VBA code to re-create the formulae in given range
'by Patrick Honorez - www.idevlop.com
'usage: from debug window, type RngToVba [L14:R14]
'                            or RngToVba range("L14:R14")
'then copy generated code to your module
    Dim c As Range
    For Each c In src
        Debug.Print "range(""" & c.Address & """).formula = """ & _
                    Replace(c.Formula, """", """""") & """"""
    Next c
End Sub

回复收藏 0 原文

~没有更多了~