带有自动填充 VBA 的 Excel Vlookup [英] Excel Vlookup with Autofill VBA

问题描述

我正在尝试制作一个看起来很简单的宏，但事实证明它比我想象的要难.我开始使用点击"方法制作它，但它并没有我想象的那么动态.基本上，我使用 vlookup 从另一个工作表中查找单元格 A2 中的名称，然后将 D 列中的数字复制到当前工作表中.查看代码时，这部分看起来像这样:

I'm trying to make a macro that seems pretty simple, but it's proven to be harder than I thought. I started making it using the "point and click" method, but it hasn't been as dynamic as I had thought. Basically I'm using vlookup to look up the names in cell A2 from another worksheet, then copy numbers in column D over to the current sheet. When looking at the code, it looks something like this for this part:

Range("D2").Select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-3],'Downstairs'!R[-1]C[-3]:R[200]C[14],4,0)"

Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D" & lastrow&)

找到第一个数字后，我会尝试自动填充其余数字.当你运行它时它会起作用，但我认为这只是因为名称是有序的.但是，如果名称不按顺序，它就不会找到正确的数字，部分原因是 R[] 和 C[] 中的所有数字随着您向下行而不断变化.如果我在列表中添加更多名称，我认为它不会起作用，这是我需要做的事情.我将 R[] 中的数字从 93 更改为 200，因为我真的不知道如何合并我之前制作的 lastrow 对象，而且我不知道另一种方法可以使这个动态.有没有更好的方法来做到这一点?

After I find the first number, I'm trying to autofill the rest. It works when you run it but I think it's just because the names are in order. However, if the names aren't in order it won't find the correct number, partly because all the numbers inside the R[] and C[] keep changing as you go down the rows. I don't think it will work if I add more names to the list, which is something I need for it to do. I changed the number inside the R[] from 93 to 200 because I don't really know how to incorporate the lastrow object I had made before, and I don't know of another way to make this dynamic. Is there a better way to do this?

推荐答案

就像我在上面的评论中提到的那样，您不需要 VBA.您可以直接在 Excel 中键入公式并进行手动自动填充.话虽如此，如果您仍然想要使用 VBA，那么只需在不需要使用自动填充的地方使用此代码即可.此代码将使用公式填充所有相关单元格.

Like I mentioned in my comment above, you do not need VBA for this. You can directly type the formula in Excel and do a manual Autofill. Having said that if you still want to do VBA then simply use this code where you do not need to use AutoFill. This code will fill all the relevant cells with formulas.

您的公式也没有给您正确的结果，因为您的表格数组不是恒定的，并且随着您向下移动而发生变化.为了防止这种情况发生，只需使用 $ 符号.

Your formula was also not giving you the right results as your table array was not constant and it was changing as you moved down. To prevent that from happening, simply use $ sign.

另一个提示:R1C1 格式如果您不熟悉它会非常混乱.坚持正常的 A1 格式.

Another Tip: R1C1 format is very confusing if you are not good with it. Stick to the normal A1 formatting.

这是实现您想要的最简单方法.请修改代码以满足您的需求

Here is the most simple way to achieve what you want. Please amend the code to suit your needs

Sub Sample()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastrow  As Long

    '~~> Change this to the releavnt sheet name
    '~~> This is the sheet where the formula will go
    Set ws1 = ThisWorkbook.Sheets("Sheet1")

    Set ws2 = ThisWorkbook.Sheets("Downstairs")

    With ws1
        '~~> Get the last row
        lastrow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Fill formula in all the relevant cells in one go
        '~~> replace my formula within quotes with your formula
        '~~> Do not forget to put the $ sign like I have done
        .Range("D1:D" & lastrow).Formula = "=VLOOKUP(A1,Downstairs!$C$1:$N$200,4,0)"
    End With
End Sub

这篇关于带有自动填充 VBA 的 Excel Vlookup的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！