如何编写一个Excel公式，将特定的值粘贴到不同的单元格中？

下面的方法使用了此处和此处所述的变通方法，使VBA中定义的工作表函数能够设置另一个单元格的值。

自定义函数在全局变量中存储了目标单元格的地址和该单元格要设置的值。然后，当工作表重新计算时触发的宏读取全局变量并将目标单元格设置为指定的值。

自定义函数的使用非常简单：

=SetCellValue(target_cell, value)

其中target_cell是工作表中单元格的字符串引用（例如 “A1"），或者是评估为此类引用的表达式。这包括像=B14这样的表达式，其中B14的值是 "A1"。该函数可用于任何有效的表达式。

SetCellValue 如果值成功写入目标单元格，则返回1，否则返回0。目标单元格中之前的任何内容都会被覆盖。

需要三段代码。

-定义SetCellValue的代码 -工作表计算事件触发的宏 -确保IsCellAddress是有效单元格地址的实用函数target_cell。

SetCellValue函数的代码

这段代码需要粘贴到插入工作簿的标准模块中。该模块可以通过Visual Basic编辑器的菜单插入，可以从功能区的Visual Basic标签中选择Developer进入。

Option Explicit

  Public triggerIt As Boolean
  Public theTarget As String
  Public theValue As Variant

  Function SetCellValue(aCellAddress As String, aValue As Variant) As Long

      If (IsCellAddress(aCellAddress)) And _
             (Replace(Application.Caller.Address, "$", "") <> _
              Replace(UCase(aCellAddress), "$", "")) Then
          triggerIt = True
          theTarget = aCellAddress
          theValue = aValue
          SetCellValue = 1
      Else
          triggerIt = False
          SetCellValue = 0
      End If

  End Function

工作表/计算 宏代码

此代码必须包含在你将使用 SetCellValue 的工作表的特定代码中。最简单的方法是在Home视图中右击工作表的标签，选择View Code，然后将代码粘贴到出现的编辑器窗格中。

Private Sub Worksheet_Calculate()

      If Not triggerIt Then
          Exit Sub
      End If
      triggerIt = False
      On Error GoTo CleanUp
      Application.EnableEvents = False
      Range(theTarget).Value = theValue
  CleanUp:
      Application.EnableEvents = True
      Application.Calculate

  End Sub

IsCellAddress函数的代码

这段代码可以粘贴到与SetCellValue代码相同的模块中。

Function IsCellAddress(aValue As Variant) As Boolean

      IsCellAddress = False

      Dim rng As Range ' Input is valid cell reference if it can be
      On Error GoTo GetOut ' assigned to range variable
      Set rng = Range(aValue)
      On Error GoTo 0

      Dim colonPos As Long 'convert single cell "range" address to
      colonPos = InStr(aValue, ":") 'single cell reference ("A1:A1" -> "A1")
      If (colonPos <> 0) Then
          If (Left(aValue, colonPos - 1) = _
                Right(aValue, Len(aValue) - colonPos)) Then
              aValue = Left(aValue, colonPos - 1)
          End If
      End If

      If (rng.Rows.Count = 1) And _
          (rng.Columns.Count = 1) And _
          (InStr(aValue, "!") = 0) And _
          (InStr(aValue, ":") = 0) Then
          IsCellAddress = True
      End If 'must be single cell address in this worksheet
      Exit Function

  GetOut:

  End Function
``` 此代码可以粘贴到与0x6&代码相同的模块中。

假设你想让文本 “文本A "显示在C5单元格中，如果B5单元格中含有 "绿色 "的值。

您可以**使用公式方法，但由于公式不能改变其他单元格中的值，因此需要在C5单元格中输入公式。

=IF(ISNUMBER(FIND("green",B5)),"Text A","")

现在，如果B5包含 "绿色 "一词，C5单元格将只显示 "文本A"。

像这样的公式可以建立在许多条件下使用。你需要定义你的要求，以便在你的特定情况下得到帮助。

如果你不想让C5有一个公式，你也可以使用VBA方法。你可以运行一个工作表更改事件，每当B5单元格发生变化时，该事件就会运行，可以通过手动编辑数值，也可以将一些东西粘贴到其中。

这样一个宏的例子可以是

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5")) Is Nothing Then
    If InStr(1, Target, "green", vbTextCompare) Then
        Target.Offset(0, 1) = "Text A"
    End If
End If
End Sub

输出的条件和位置只是一个例子，当然，需要根据你的要求进行调整。

公式和宏方式的区别在于

–采用公式方式时，C5单元格会包含一个公式。如果用户不小心删除了公式，它所提供的功能也会被删除。(不过也有办法管理) –采用VBA宏的方式，单元格C5将不显示任何公式，会有逐字文本作为值，但改变设置需要Excel VBA的知识。另外，采用VBA方式，必须将工作簿保存为启用宏的工作簿，用户必须允许使用宏或将该文件设为可信文件。

注：以上只是一个例子。你需要定义你的要求，是评估数字还是文本，评估是否区分大小写，评估规则是什么，结果放在哪里等等。

要检查一个条件是否得到满足，在某个单元格中写一个IF公式来更新 “不同单元格中的某一行文字"。结果单元格将只包含公式的值，而不是生成该值的公式，就像这样。

工作单元格(如J5) =IF(A1="yes","Specific line of text","") 结果单元格(如B5) =J5

所以如果条件满足(A1="是”)，B5将包含 “特定行文字"。否则它将保持空白。

注意： 具有可变值的单元格通常会包含某种公式来不断更新它的值。

如果你不想让任何人知道创建值的真正公式，公式可以在其他单元格中生成值，然后复制到结果单元格中，例如B5。

要隐藏生成值的公式或隐藏B5所指向的单元格(继续检查是否需要更新值)，请参考。 https://support.office.com/en-us/article/display-or-hide-formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f

其他可以达到同样效果的函数包括CHOOSE, HLOOKUP, LOOKUP, VLOOKUP。

=value(cell)

直接用公式，遇到同样的问题，也能用。