☰

如何根据不同单元格中的特定条目向单元格添加自定义文本？

tag-icon • tag-icon microsoft-excel vba
如何根据不同单元格中的特定条目向单元格添加自定义文本？

我的实验室能够进行 20 多种不同的分析，我们从大约 15 家公司获得合同，进行这些分析的组合。我创建了一个 Excel 电子表格来跟踪工作，其中列是我们可以进行的 20 种不同的分析，行是公司。我输入复选标记或“NA”，具体取决于该公司是否要求进行该特定分析。（每家公司都要求自己的分析组合）。

我需要以下方面的帮助：

如果我在单元格 A100 中输入“公司 1”，我希望单元格 B100 显示“NA”。如果我输入“公司 2”，我希望单元格 D100 显示“NA”。如果我输入“公司 3”，则什么也不做。我可以手动添加复选标记，因为还有其他变量不需要提及。

现在，我已经能够在一定程度上用 VBA 开发一些玩具解决方案（请参阅下面的代码）。但是，我有两个问题：

  1. 为了运行代码，我必须切换到 VBA 编辑器并在每次输入后按 F5。相反，我希望它像使用单元格公式一样工作。换句话说，如果我在 A 列的任何单元格中输入“公司 1”并按“Enter”，我希望“NA”自动显示在行上的相应单元格中。我想我可以为此录制一个宏，但该文件与许多人共享，我宁愿避免这样做。

  2. 将来我需要添加更多公司和分析，因此我需要一个可以快速进入和更新的代码。或者也许有一个我添加的公司列表，并以某种方式将其链接到我的代码。

    Sub writeNA()
For i = 1 To 20 Step 1

x = Cells(i, 1).Value

If x = "Company 1" Then
 Cells(i, 2).Value = "NA"
End If

If x = "Company 2" Then
 Cells(i, 3).Value = "NA"
End If

If x = "Company 3" Then
 Cells(i, 4).Value = "NA"
End If

Next
End Sub

谢谢你！

答案1

你可以用公式来做到这一点，只要你不需要编辑结果

  • 创建一个表格作为各个公司的模板。
    • 这可以放在单独的工作表上，如果您愿意，甚至可以隐藏它。
  • NAME以表格为例：tblTests

它可能看起来像：

在此处输入图片描述

  • 以类似的方式设置您的工作表，在 A 列中填写公司名称，顶部列出测试。
  • 在B2工作表中输入公式：

对于O365：

=IFERROR(FILTER(tblTests[[Test1]:[Test20]],tblTests[Company]=A2),"")

对于早期版本：

=IFERROR(INDEX(tblTests[[Test1]:[Test20]],MATCH(A4,tblTests[Company],0),0),"")

并根据需要向下填充。

（如果您的 Excel 版本没有动态数组，您可能需要将其作为数组公式输入到整个 20 个单元格的行段中）

如果您现在在 A 列中输入公司名称，它将自动按照您设置的模式填充行。

在此处输入图片描述

如果这必须是一个宏，或者如果你可以填写完单元格后需要修改，您可以使用类似的方法，使用引用您在工作簿或代码中设置的查找表的宏，并让针对worksheet_changeA 列的事件触发该宏

例如，在Worksheet Code您记录公司的工作表中输入以下代码：（请注意，我们仍在工作簿中的某些工作表上使用模板表）

Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range, C As Range, V, I As Long, J As Long
    Dim tblTests As Variant

Set R = Columns(1)
Set C = Intersect(Target, R)
If Not C Is Nothing Then
    If C.Count > 1 Then
        MsgBox "Enter only one company at a time"
        Exit Sub
    End If
    
    'tblTests is on Sheet1
    tblTests = Sheet1.ListObjects("tblTests").DataBodyRange
    
    Application.EnableEvents = False
        With C
            Range(.Cells(1, 2), .Cells(1, 21)).ClearContents
                'with only 20 companies, can loop the table
                'if you have thousands, may be more efficient to read the lookup table into a dictionary
                I = 1
                Do Until tblTests(I, 1) = C.Value
                    I = I + 1
                    If I > UBound(tblTests, 1) Then Exit Do
                Loop
                
                If I > UBound(tblTests, 1) Then
                    If Len(C) > 0 Then .Offset(0, 1) = "Company not in template table"
                Else
                    ReDim V(1 To 21)
                    For J = 1 To 21
                        V(J) = tblTests(I, J)
                    Next J
                    
                    .Resize(columnsize:=21) = V
                End If
            End With
    Application.EnableEvents = True
End If

End Sub

答案2

我想，我还没有明白，否则对我来说它就像一个简单的公式

在 B100 中输入公式 = IF(A100="Company 1","NA","")

在 D100 中输入公式 = IF(A100="Company 2","NA","")

谢谢。

相关内容