英文:
Using VBA to insert a formula using existing cell reference
问题
我了解如何在VBA中记录宏。
我有一堆单元格，每个单元格引用其他工作表上的单元格（例如：=R!$A$1）。这些单元格与目标单元格不匹配（例如，主工作表上的D3包含=R!$A$1，下一个条目是F3引用=R!$A$4）。
我尝试使用VBA将第一个实例替换为=IF(R!$A$1=""，"TBA"，R!$A$1)。这个宏记录下来并用于特定单元格，但当我移动到F3并运行宏时，它将单元格替换为完全相同的公式。如何让VBA使用单元格中的现有文本作为替换文本，而不是盲目地复制第一个示例？
我的第一个想法是ActiveCell.R1C1 = "=IF( ActiveCell.R1C1 = "", "TBA", ActiveCell.R1C1)，但这无法编译。
谢谢！
英文:
Okay, I have a basic understanding of how to record macros in VBA.
I have a bunch of cells, each referencing cells on other sheets. (Ex: =R!$A$1). These cells don't match the destination cells (for example, D3 on the primary sheet contains =R!$A$1, and the next entry is F3 referencing =R!$A$4).
Using VBA, I tried replacing the first instance with =IF(R!$A$1="", "TBA", R!$A$1). This recorded and worked for the cell in question, but when I moved to F3 and ran the macro, it replaced the cell with the exact same formula. How do I get VBA to take the existing text in the cell as replacement text, instead of blindly copying the first example?
My first thought was ActiveCell.R1C1 = "=IF( ActiveCell.R1C1 = "", "TBA", ActiveCell.R1C1) but that failed to compile.
Thanks!
答案1
得分: 0
尝试类似这样的代码（如果我理解你想要做什么...）
Sub Test()
Dim frm As String
If ActiveCell.HasFormula Then '单元格中有公式？
frm = ActiveCell.Formula '读取现有的公式
frm = Right(frm, Len(frm) - 1) '移除 `=`
ActiveCell.Formula = _
"=IF(" & frm & "="""",""TBA""," & frm & ")" '设置新的公式
End If
End Sub
英文:
Try something like this (if I understand what it is you're trying to do...)
Sub Test()
Dim frm As String
If ActiveCell.HasFormula Then 'formula in cell?
frm = ActiveCell.Formula 'read the existing formula
frm = Right(frm, Len(frm) - 1) 'remove the `=`
ActiveCell.Formula = _
"=IF(" & frm & "="""",""TBA""," & frm & ")" 'set new formula
End If
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论