vba/excel - 根据用户输入到 sheet1 上的单元格，从 sheet2 中的值填充 sheet1 中的单元格

vba/excel - populate cells in sheet1 from values in sheet2 based on user input into cells on sheet1

提问by user2292941

I have created a generic excel file to help demonstrate what I'm looking to do. The file I've named Tool.xlsm contains two worksheets; Sheet1 and Sheet2. Sheet1 will be designed to have a few fields which will accept user input. Sheet2 will be hidden from the user, but will contain the various drop down list selection options and their corresponding descriptions which should be displayed in another cell on Sheet1 when a specific code is selected. Additionally, Sheet2 will contain numerous ID#s in one column, and their corresponding usernames in the next column. The purpose of this is for the user to be able quickly associate an ID# with user it belongs to.

我创建了一个通用的 excel 文件来帮助演示我想要做什么。我命名为 Tool.xlsm 的文件包含两个工作表；Sheet1 和 Sheet2。Sheet1 将被设计为具有一些接受用户输入的字段。Sheet2 will be hidden from the user, but will contain the various drop down list selection options and their corresponding descriptions which should be displayed in another cell on Sheet1 when a specific code is selected. 此外，Sheet2 将在一列中包含多个 ID#，在下一列中包含它们相应的用户名。这样做的目的是让用户能够快速将 ID# 与其所属的用户关联起来。

Here is what I have so far...I doubt I'm going about it as efficiently as I should be, but I'd greatly appreciate your all's expertise!

到目前为止，这是我所拥有的......我怀疑我是否能像我应该的那样有效地进行，但我非常感谢你们所有人的专业知识！

Sub Button1_Click()

'Based on selected value of C1, show corresponding message in J1'
'Can this be done by simply referencing the code descriptions in sheet2?'

If Range("C1") = "code 1" Then
    Range("J1") = "code 1 description"
End If

If Range("C1") = "code 2" Then
    Range("J1") = "code 2 description"
End If

'End of code selection'
End Sub

Sub Button2_Click()

'Based on ID# entered into C3, display corresponding name in J1 (Sheet2 contains ID#s with corresponding names)'
'There has to be an esier way to loop through 1000s of records and display corresponding ID# and Person''s name'
'Rather than assigning Person 1, to Range J1, I should be able to just reference the cell Sheet2!E3 but that doesn''t seem to work'

If Range("C3") = "1001" Then
    Range("J1") = "Person 1"
End If

If Range("C3") = "34349090" Then
    Range("J1") = "Person 83"
End If

'End ID# search'
End Sub

Sub Button3_Click()

'Clear unlocked cells'

End Sub

my file in dropbox

我在保管箱中的文件

采纳答案by Santosh

you can use the worksheet_change event. Kindly set rngFindCode & rngFindCode1 range accordingly to refer to your data in sheet2.

您可以使用 worksheet_change 事件。请相应地设置 rngFindCode 和 rngFindCode1 范围以参考您在 sheet2 中的数据。

Below is the code.

下面是代码。

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next

    Application.EnableEvents = False

    If Target.Address = "$C" And Target.Cells.Count = 1 And Target.Value <> "" Then

        Dim rngFindCode As Range '
        Dim cellCode As Range

        Set rngFindCode = Sheets("Sheet2").Range("C1:C100")    ' Refers to range where code is in sheet 2
        Set cellCode = rngFindCode.Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)

        If Not cellCode Is Nothing Then
            Range("J1").Value = cellCode.Offset(0, 1).Value
        End If

    ElseIf Target.Address = "$C" And Target.Cells.Count = 1 And Target.Value <> "" Then

        Dim rngFindCode1 As Range '
        Dim cellCode1 As Range

        Set rngFindCode1 = Sheets("Sheet2").Range("E1:E100")    'Refers to range where name is
        Set cellCode1 = rngFindCode1.Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)

        If Not cellCode1 Is Nothing Then
            Range("J1").Value = cellCode1.Offset(0, 1).Value
        End If

    Else
        Range("J1").Value = ""
    End If

    Application.EnableEvents = True
End Sub

回答by David Zemens

To your queries:

对您的查询：

Can this be done by simply referencing the code descriptions in sheet2?

这可以通过简单地引用 sheet2 中的代码描述来完成吗？

Yes. You can use VLOOKUPformula for this.

是的。您可以VLOOKUP为此使用公式。

Likewise, you could use the VLOOKUPformula to return the names based on the IDs.

同样，您可以使用VLOOKUP公式根据 ID 返回名称。

E.g., assume your usernames are in column K and ID's in column J:

例如，假设您的用户名在 K 列中，ID 在 J 列中：

On sheet 1, assuming the ID in cell C3, enter the formula: =VLOOKUP(C3, Sheet2!$J$K, 2, False)

在工作表 1 上，假设 ID 在单元格 C3 中，输入公式： =VLOOKUP(C3, Sheet2!$J$K, 2, False)