我正在尝试创建一个下拉列表,如果您在单元格中选择某个员工 ID,它会"A2"
用三位员工数字 ID 填写单元格"B2"
,"C2"
填写完整的员工姓名。这应该是一个相当简单的任务,但是我编写的代码根本不起作用。运行它后,它会在"B2"
中放置一个"0"
,而不管"A2"
中的选择如何,而"C2"
什么都没有。我试图玩弄.Value/.Text/.Number
标识符,并在值周围使用""
或没有""
,但无济于事。知道我做错了什么吗?
Sub ind_naming()
Dim in_ws As Worksheet
Set in_ws = ActiveWorkbook.Sheets("INDIVIDUAL_REPORT")
in_ws.Range("A5:E100").Delete Shift:=xlUp
in_ws.Range("A5:E100").Interior.Color = RGB(224, 245, 250)
in_ws.Range("F1").Value = in_ws.Range("A2").Value
If in_ws.Range("A2").Value = "BI08298" Then in_ws.Range("B2").Value = "113" And in_ws.Range("C2").Value = "John Smith"
If in_ws.Range("A2").Value = "GB31446" Then in_ws.Range("B2").Value = "170" And in_ws.Range("C2").Value = "John Bob"
If in_ws.Range("A2").Value = "ZB40049" Then in_ws.Range("B2").Value = "158" And in_ws.Range("C2").Value = "Gary Doe"
If in_ws.Range("A2").Value = "KF14006" Then in_ws.Range("B2").Value = "121" And in_ws.Range("C2").Value = "Mary Jane"
If in_ws.Range("A2").Value = "JT99623" Then in_ws.Range("B2").Value = "167" And in_ws.Range("C2").Value = "Nate Tailor"
If in_ws.Range("A2").Value = "FO07386" Then in_ws.Range("B2").Value = "168" And in_ws.Range("C2").Value = "Jacob Grant"
End Sub
我认为你应该使用Vlookup。我想你在某个地方有(或你应该有(桌子,让我们在工作表名称中说"Emps"。此表应如下所示:
Emp_ID Emp_Number Emp_Name
BI08298 113 John Smith
然后,您可以将以下公式放入B2中:
=IFERROR(VLOOKUP(A2,Emps!A:C, 2, 0),"")
在 C2 中:
=IFERROR(VLOOKUP(A2,Emps!A:C, 3, 0),"")
公式也可以通过VBA插入。我会在工作表下(不在模块中(制作一个宏,类似的东西;
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$2" Then Exit Sub
Dim sFormulaB As String, sFormulaC As String
sFormulaB = "=IFERROR(VLOOKUP(A2,Emps!A:C,2, 0),"""")"
sFormulaC = "=IFERROR(VLOOKUP(A2,Emps!A:C, 3, 0),"""")"
With Target
.Offset(, 1).Formula = sFormulaB
.Offset(, 2).Formula = sFormulaC
End With
Call RestOfCode_procedure
End Sub
您可以将其余代码存储在 sub 的 normal 模块中,并从上面的过程中调用它。
您也可以使用 Case 语句来执行此操作,但我可能会在单独的工作表中进行 Vlookup 查找,您可以在单独的工作表中查找员工的所有详细信息,这样您将减少代码行数,如果该工作表上的任何细节发生变化,您的代码也会发生变化,但对于 Case 语句,请参见下文:
Sub ind_naming()
Dim in_ws As Worksheet
Set in_ws = ActiveWorkbook.Sheets("INDIVIDUAL_REPORT")
in_ws.Range("A5:E100").Delete Shift:=xlUp
in_ws.Range("A5:E100").Interior.Color = RGB(224, 245, 250)
in_ws.Range("F1").Value = in_ws.Range("A2").Value
Select Case in_ws.Range("A2").Value
Case "BI08298"
in_ws.Range("B2").Value = "113"
in_ws.Range("C2").Value = "John Smith"
Case "GB31146"
in_ws.Range("B2").Value = "170"
in_ws.Range("C2").Value = "John Bob"
Case "ZB40049"
in_ws.Range("B2").Value = "158"
in_ws.Range("C2").Value = "Gary Doe"
Case "KF14006"
in_ws.Range("B2").Value = "121"
in_ws.Range("C2").Value = "Mary Jane"
Case "JT99623"
in_ws.Range("B2").Value = "167"
in_ws.Range("C2").Value = "Nate Tailor"
Case "FO07386"
in_ws.Range("B2").Value = "168"
in_ws.Range("C2").Value = "Jacob Grant"
End Select
End Sub