英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论