vba 在宏中使用 Vlookup

Using VLookup in a macro

提问by user1935623

I'm new to VBA but I'm hooked! I've created a workbook that tracks overtime in 2 week blocks with one 2-week block per worksheet. The macro I'm trying to debug is designed to carry any changes made in a worksheet over to following worksheets. The trick is that the data in one row may be in a different row in following worksheets so I trying to use VLookup in a macro to keep it accurate.

我是 VBA 新手，但我迷上了！我创建了一个工作簿，它以 2 周的时间块跟踪加班时间，每个工作表有一个 2 周的时间块。我正在尝试调试的宏旨在将工作表中所做的任何更改传递到以下工作表。诀窍是一行中的数据可能在以下工作表中位于不同的行中，因此我尝试在宏中使用 VLookup 以保持其准确性。

Sub CarryForward()
    Dim Answer As String
    Answer = MsgBox("This should only be used for a PERMANENT crew change." & vbNewLine & "If you are adding a new person to the list," & vbNewLine & "please use the Re-Sort function." & vbNewLine & "Do you want to continue?", vbExclamation + vbYesNo, "Caution!")
    If Answer = vbNo Then
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Dim ActiveWorksheet As String
    ActiveWorksheet = ActiveSheet.Name
        For i = (ActiveSheet.Index + 1) To Sheets("DATA").Index - 1
            For x = 5 To 25
                Dim a As String
                Dim b As String
                a = "B" & x
                b = "C" & x
                ActiveSheet.Range(b).Value = Application.WorksheetFunction.VLookup(a, Sheets(ActiveWorksheet).Range("B5:C25"), 2, False)
            Next x
            Range("A3").Select
        Next i
    Sheets(ActiveWorksheet).Select
    Application.CutCopyMode = False
    Range("A3").Select
    Application.ScreenUpdating = True
End Sub

I'm pretty sure it's just a syntax error in the VLookup line of code. A lot of the help posted comes close to what I'm looking for, it just doesn't get me over the finish line.

我很确定这只是 VLookup 代码行中的语法错误。发布的许多帮助与我正在寻找的内容非常接近，但它并没有让我越过终点线。

Any help would be appreciated!

任何帮助，将不胜感激！

回答by chris neilsen

It is a little unclear what you are trying to do, but reading between the lines I think

有点不清楚您要做什么，但我认为在字里行间阅读

• you want to lookup the value contained in cell named by a?
• and put the result on sheet index i?

• 您想查找由 命名的单元格中包含的值a吗？
• 并将结果放在工作表索引上i？

Also, there is a lot of opportunity to improve your code: see imbedded comments below

此外，还有很多机会可以改进您的代码：请参阅下面的嵌入评论

Sub CarryForward()
    Dim Answer As VbMsgBoxResult  ' <-- Correct Datatype
    Answer = MsgBox("This should only be used for a PERMANENT crew change." & vbNewLine & _
        "If you are adding a new person to the list," & vbNewLine & _
        "please use the Re-Sort function." & vbNewLine & _
        "Do you want to continue?", _
        vbExclamation + vbYesNo, "Caution!")
    If Answer = vbNo Then
        Exit Sub
    End If
    Application.ScreenUpdating = False
    ' Dim ActiveWorksheet As String  <-- Don't need this
    'ActiveWorksheet = ActiveSheet.Name <-- use object variables
    Dim wbActive As Workbook  ' <-- don't select, use variables for sheet objects
    Dim shActive As Worksheet
    Set wbActive = ActiveWorkbook
    Set shActive = ActiveSheet
    'Dim a As String ' <-- no point in putting these inside the loop in VBA.  And don't need these anyway
    'Dim b As String
    Dim SearchRange As Range
    Set SearchRange = shActive.Range("B5:C25") ' <-- Use variable to hold range
    Dim shDest As Worksheet
    Dim i As Long, x As Long '<-- dim all your variables
    For i = (shActive.Index + 1) To wbActive.Worksheets("DATA").Index - 1 ' <-- qualify references
        Set shDest = wbActive.Sheets(i)
        For x = 5 To 25
            'a = "B" & x <-- no need to create cell names
            'b = "C" & x
            ' I think you want to lookup the value contained in cell named by a?
            ' and put the result on sheet index i?
            '  Note: if value is not found, this will return N/A.  Add an error handler
            wbActive.Sheets(i).Cells(x, 3).Value = Application.VLookup(shActive.Cells(x, 2).Value, SearchRange, 2, False)
        Next x
        'Range("A3").Select
    Next i
    'Sheets(ActiveWorksheet).Select ,-- don't need these
    'Application.CutCopyMode = False
    'Range("A3").Select
    Application.ScreenUpdating = True
End Sub

回答by Sam

I suspect you would want to replace the vlookup statement to be something like

我怀疑您想将 vlookup 语句替换为类似

Application.WorksheetFunction.VLookup(ActiveWorksheet.Range(a).value, ActiveWorksheet.Range("B5:C25"), 2, False)

at the moment it looks like you're just doing a vlookup against some strings B5, B6, B7 etc instead of values in those cells

目前看起来您只是在对某些字符串 B5、B6、B7 等而不是这些单元格中的值进行 vlookup