活动 VBA 宏，用于对工作簿中的单独工作表执行 vlookup

Question

我被要求这样做，而不是在单元格内的工作表本身。

我需要一个持续运行的宏，以便当我在工作表 9 的单元格 D1 中输入 ID 号时，工作表 1 中的各种其他单元格由工作表 2 中表格中的数据点填充。

我有以下几点：

此外，Excel 不断崩溃这样做，但我的指示是专门使用 VBA 而不是在单元格中使用正常的查找。

尝试将其设置为常规和其他内容。 对 VBA 非常陌生 抱歉

Private Sub Worksheet_Change(byVal Target As Range)
Dim ID As String
Dim LookupRange As Range
Set LookupRange = Sheet3.Range("A13:AN200")
Dim DataValue As String
If Sheets("Template").Range("D9").Value <> "" Then
     ID = Sheets("Template").Range("D9")
     DataValue = Application.WorksheetFunction.Vlookup(ID, LookupRange, 3, False)
     Range("D11").Value = DataValue
End if
End

Answer 1

我查看了您的代码并进行了一些更改，使其能够工作。我已经评论了我所做的大部分工作。如果您有任何疑问，请告诉我。

免责声明：这是未经测试的。因此，您需要在实际使用它之前对其进行验证。

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim ws3 As Worksheet
        Dim wsName As String
        Dim IDRange As String
        Dim ResultRange As String
        Dim vLookUpRange As String
        Dim ID As String
        Dim LookupRange As Range
        Dim DataValue As String
        wsName = "Template"
        IDRange = "D9"
        ResultRange = "D11"
        vLookUpRange = "A13:AN200"
        'This is just a habbit of mine, I always set sheets to their own variables.
        'It is just easier for me to work with
        Set wb = ActiveWorkbook
        Set ws = wb.Worksheets(wsName)
        Set ws3 = wb.Worksheets(3)
        'This line (moved from below Dim) was not writen correctly. it is not Sheet3 but sheets(3) As you can see I moved
        'the sheet definition to above. (Again a habbit of mine)
        Set LookupRange = ws3.Range(vLookUpRange)
        'This is not needed but I add it when I am working with changes to sheets so that I only run the code I want
        'when it is within the rang I am looking for. You could add logic to make sure that you only run the code if
        'you are only modifying that spesific cell. But for your goal, I don't think it is needed.
        If Not Intersect(Target, ws.Range(IDRange)) Is Nothing Then
            'You can use .Value but .Value2 is slightly faster with very few consequences.
            'eg if you ever need to read or write large amounts of data it will save you some time.
            If ws.Range(IDRange).Value2 <> "" Then
                ID = ws.Range(IDRange)
                DataValue = Application.WorksheetFunction.VLookup(ID, LookupRange, 3, False)
                'You also need to specify a sheet for this. Since this is located in the sheet you are entering
                'data I assumed the sheet "template"
                ws.Range(ResultRange).Value = DataValue
            End If
        End If
    End Sub