excel - 根据单元格选择选择适当的公式

问题描述

我正在尝试创建一个宏，其中选定的单元格（或单元格范围）将根据其位置在其中包含一个公式。我的问题是我试图从 3 种不同的公式格式中进行选择。

例如：我想在 R10C2:R20C2 范围内选择单元格（或范围）时使用公式“=R5C*RC[1]”

当在 R21C2:R30C2 范围内选择单元格（或范围）以使用公式“=R3C+RC[1]”时

当在 R31C2:R40C2 范围内选择单元格（或范围）以使用公式时

"=IF(R2C7,R3C8*RC[1],R3C9*RC[1])"

我对 VBA 完全陌生，我所拥有的只是以下内容；附加行将相邻单元格从公式转换为值：

Sub FormlaOptn()

  Selection.FormulaR1C1 = "=R5C*RC[1]"
  Selection.Offset(0, 1).Copy
  Selection.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
  Application.CutCopyMode = False

 End Sub

标签： excelvba

您需要确定您的选择（或任何指定范围）是否在您的 3 个较大范围内（或不在任何范围内）。然后，您可以使用 if / elseif / else 或 select case 语句。

Excel 无法使用您记下的范围引用。Excel 使用像“A1”这样的单元格区域名称，其中字母代表列，数字代表行。一个多单元格范围，看起来像“A1:C5”。您可能还会发现混合了美元（“%”）符号。这些符号表示绝对的列和行引用，我不会在本回复中介绍。为方便起见，我提供了一个简单的函数 RangeMaker，用于将完全数字的行和列范围定义转换为 Excel 样式。这不是典型的，但我不想把地毯从你下面完全拉出来。

鉴于您的范围引用不属于正常的 Excel 术语，我遗漏了一些细节。因此，在下面的示例代码中，我冒昧了。您必须根据您的预期范围进行调整。对于此代码，我假设以下电子表格数据和布局。

B 列 - 可能包含公式的列。只有第 10 到 40 行会自动更新。其他行将被忽略。
C 列 - 用于保存与 B 列中的公式结果匹配的值的列
D、E 和 F 列 - 包含可用于 B 列公式的数字表达式的列。
G 列 - 可以在 B 列的公式中使用的布尔（真/假）表达式。

我用 3 个函数或过程加上一个附加过程 FormlaOptnDemo 解决了这个问题，它允许您测试或演示主过程 FormlaOptn。

Option Explicit

' The following function tells you if one range is contained in another. You will see why we use this later.

Function RangeAContainsRangeB(RangeA As Range, RangeB As Range)
' Returns True if RangeB is entirely within RangeA, else returns False.
' https://docs.microsoft.com/en-us/office/vba/api/Excel.Application.Intersect
    Dim rng As Range
    Set rng = Intersect(RangeB, RangeA)
    RangeAContainsRangeB = False
    If rng Is Nothing Then
        Exit Function
    ElseIf RangeB.Address = RangeA.Address Or rng.Address = RangeB.Address Then
        ' RangeB is completely within RangeA
        RangeAContainsRangeB = True
    End If
End Function


' You may not need the following function.  I included it, because you
' seem to want to define ranges strictly using row and column numbers
' instead of the normal Excel range address convention, CR, where C is
' an alpha representation of the Column and R is the row number.  An
' example is A2, which is the first column and the second row.

Function RangeMaker(intRow1 As Integer, intColumn1 As Integer _
                    , intRow2 As Integer, intColumn2 As Integer) As Range
    ' Convert two row / column number pairs to an Excel Range object.
    ' RangeMaker(2,3,9,11).Address returns Range [$C$2:$K$9]
    Set RangeMaker = Range(Cells(intRow1, intColumn1).Address _
                           & ":" & Cells(intRow2, intColumn2).Address)
End Function


Sub FormlaOptnDemo()
    Dim ans As String
    ' This is a wrapper around the main procedure.  It allows
    ' you to run the procedure on any arbitrary range.
    ' Default is all cells in the 3 ranges.
    
    'Prompt user for a range by name
    ans = InputBox(Prompt:="Enter a range name: " _
                   , title:="", Default:="B10:B40")
    
    ' Convert address string to range object
    ' and pass it to FormlaOptn
    Call FormlaOptn(Range(ans))
End Sub

Sub FormlaOptn(Optional rngTarget As Range)

    Dim cell As Range, rng1 As Range
    Dim rng2 As Range, rng3 As Range
    Dim blnSetFormula As Boolean
    Dim s As String
    
    
    ' If rngTarget has multiple columns, this procedure
    ' will have unintended results.  Raise an exception.
    If rngTarget.Columns.Count > 1 Then
        Err.Raise 1000, "FormlaOptn", "rngTarget must not contain" _
                    & " more than a single column."
    End If
    
    ' Debug.Print statements are in for the
    ' developer. Remove them before going "live."
    Debug.Print vbCrLf, vbCrLf, vbCrLf
    ' If not rngTarget provided, use Selection.
    ' Note that Selection may contain more than one cell.
    If rngTarget Is Nothing Then Set rngTarget = Selection
    Debug.Print "Target: ", rngTarget.Address
        
    ' Define the 3 ranges for which you want
    ' different formulae applied.
    Set rng1 = RangeMaker(10, 2, 20, 2)  ' $B$10:$B$20
    Debug.Print "Range 1: ", rng1.Address
    
    Set rng2 = RangeMaker(21, 2, 30, 2)  ' $B$21:$B$30
    Debug.Print "Range 2: ", rng2.Address
    
    Set rng3 = RangeMaker(31, 2, 40, 2)  ' $B$31:$B$40
    Debug.Print "Range 3: ", rng3.Address
    
    ' For each cell in rngTarget (the passed argument),
    ' check to see if it falls in the 3 ranges, and
    ' if so, apply the appropriate formula.
    
    For Each cell In rngTarget
        ' If the cell does not have a formula already,
        ' we won't bother making any updates.
        If cell.HasFormula Then
            ' Until we set a formula, we know we haven't,
            ' so blnSetFormula is False.  :)
            blnSetFormula = False
            If RangeAContainsRangeB(rng1, cell) Then
                ' The cell falls in range of cells to which we
                ' want to apply the first formula.
                cell.Formula = "=" & cell.Offset(0, 2).Address _
                               & "*" & cell.Offset(0, 3).Address
                ' Let's remember that we successfully set a
                ' for this cell.
                blnSetFormula = True
            ElseIf RangeAContainsRangeB(rng2, cell) Then
                cell.Formula = "=" & cell.Offset(0, 2).Address _
                               & "+" & cell.Offset(0, 3).Address
                blnSetFormula = True
            ElseIf RangeAContainsRangeB(rng3, cell) Then
                ' I'm certain this is not the exact formula you
                ' wish to use, but you can use it as a template.
                cell.Formula = "=IF(" & cell.Offset(0, 5).Address & ", " _
                                & cell.Offset(0, 2).Address & "*" _
                                & cell.Offset(0, 3).Address & ", " _
                                & cell.Offset(0, 3).Address & "*" _
                                & cell.Offset(0, 4).Address & ")"
                blnSetFormula = True
            End If
            
            ' If we set a formula for the current cell, let's copy
            ' its value to the cell to its right.
            If blnSetFormula Then
                Debug.Print "Right of Target: ", cell.Address
                rngTarget.Offset(0, 1).Value = cell.Value
            End If
    
        End If
    
    Next cell

End Sub

excel - 根据单元格选择选择适当的公式

问题描述

解决方案

推荐阅读