☰

如果 Microsoft Excel 中满足条件，如何附加到公式？

tag-icon • tag-icon microsoft-excel vba microsoft-excel-2007 macros automation
如果 Microsoft Excel 中满足条件，如何附加到公式？

我的 Excel 选项卡中分布着一堆公式，其形式如下：

=cell_1+0.0049>SUM(cell_2:cell_3)例如-

=O10+0.0049>SUM(H10:N10)

我想找到这些单元格的所有实例并附加以下内容：

-OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-6)

所以我的例子就变成了-

=O10+0.0049>SUM(H10:N10)-OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-6)

有没有在 excel 或 vba 中简单的方法可以做到这一点？

0.049>SUM对于我想要附加到的单元格来说，这是唯一的。

到目前为止我已经设法做的是使用以下命令识别我想要附加的单元格：

0.049>SUM*通配符*可以帮助我遍历所有需要附加的单元格，但我不确定如何在整个工作表上执行此操作以及如何附加？

编辑：我已经设法让 VBA 选择相关的单元格，但我不确定如何让它附加我想要的位以及如何在整个工作表上执行此操作。

Cells.Find(What:="0.0049>SUM", After:=ActiveCell, LookIn:=xlFormulas2, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

答案1

我们可以用来.Find查找第一个出现的位置，然后.FindNext继续查找其他出现的位置。

范围.查找

范围.查找下一个

我已经使用了ws.Cells，但您可以轻松地将其更改为，Selection以便它仅在选定的单元格内搜索。

Option Explicit

Public Sub AppendToFormula()

Dim find_in_formula As String
Dim append_to_formula As String
Dim found_cell As Range
Dim first_address As String
Dim ws As Worksheet

'the value you want to find in the formulas on the active sheet
find_in_formula = "0.049>SUM"

'the value you want to append to each found formula
append_to_formula = "-OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-6)"

'the currently active sheet
Set ws = ActiveSheet

With ws.Cells
    
    'find the first occurrence of the formula
    Set found_cell = .Find(find_in_formula, , xlFormulas)
    
    'if we have found the formula
    If Not found_cell Is Nothing Then
    
        'store the location of the first occurrence of the formula
        first_address = found_cell.Address
        
        
        Do
            'append the text to the end of the formula
            found_cell.Formula2 = found_cell.Formula2 & append_to_formula
            
            'look for the next occurrence of the formula
            Set found_cell = .FindNext(found_cell)
            
            'when find_next reaches the end of the search, it wraps around
            'to the beginning. If we have returned to the original cell
            'then stop - we're done
            If found_cell.Address = first_address Then Exit Do
            
            'keep searching until nothing is found
        Loop While Not found_cell Is Nothing
    End If
End With


End Sub

相关内容