上下文
我正在尝试制作一份无麻烦的多户租赁分析工作表。最终用户对excel一无所知。一节的数据点为
单位类型
,则,
每种类型的单元数
,则,
单位月租金
和
单位年租金
。我希望用户能够输入
月租
并拥有
年租金
计算或反之亦然(假设在这两种情况下,他们也输入了
件数
每种类型)。所讨论的公式比你想象的要简单:
月租金
=
年租金
/
12
/
单位数量
年租金
=
月租金
*
单位数量
*
12
我启用了迭代计算,使我可以在每个租金列中保留一个公式,因为它们在计算时相互依赖(
从属公式
).这很好,但只适用于第一次进入。如果用户输入
月租
数据,但现在想输入
年度数据
,则月度计算公式已不存在。
然而
我在中使用了一些狡猾的代码来解决这个问题
Private Sub Worksheet_Change(ByVal Target As Range)
我贴在下面的。这非常有效。无论何时更改其中一个,都会为另一个插入公式,覆盖用户输入--
除非
。。。其中一个公式已删除,而不是刚刚更改。
问题所在
我找不到有关用户删除公式时如何插入公式的任何信息。例如:假设用户之前输入了
月租
工作表自动计算年租金。现在,当用户返回删除
月租
,月租单元未重置,为空。公式不再存在,当他们编辑年租金时,VBA不会恢复月租金公式。
我不知道:
当用户删除其中一个相关公式时,理想的行为是让单元格返回到其起始位置,同时放置两个圆形公式。似乎只要在删除时重新输入公式就可以了,但令人惊讶的是,我找不到任何帮助。
Private Sub Worksheet_Change(ByVal Target As Range)
'Macro replaces formulas in circular reference cells
'This is to allow users to enter a piece of data in one
'column and have the other column automatically calculate,
'even if they had already entered data into the cell that
'calculates.
'
'FOR EXAMPLE: users can enter the monthly rent to have the
'annual rent calculate OR they can enter the annual rent to
'have the monthly rent calculate (assuming they have also
'provided number of units in the No. of Units column). This
'macro overwrites the cell contents of the unused column, allowing
'users to enter a monthly rent figure and see what the annual rent
'is but then to specify the annual rent and have the monthly rent
'column overwrite their previously entered figure
Dim AnnualRent, MonthlyRent As Range
Dim cll As Variant
'Dynamically set the ranges of interest, to allow users to
'add rows willy-nilly
Set AnnualRent = Range("R2:R" & Range("Total_Ann_Rent").Row - 1)
Set MonthlyRent = Range("P2:P" & Range("Total_Ann_Rent").Row - 1)
'It is necessary to disable event listening to prevent an infinite loop
Application.EnableEvents = False
'Handle subsequent changes to the ranges set above, specifically,
'to rebuild the circularity
For Each cll In Target.Cells
With cll
'Make a persistent formula for MonthlyRent
If Not Intersect(cll, MonthlyRent) Is Nothing _
And .Offset(0, 2).FormulaR1C1 <> "=RC[-2]*12*RC[-11]" Then
.Offset(0, 2).FormulaR1C1 = "=RC[-2]*12*RC[-11]"
' 'Reinstate the MonthlyRent when it's deleted
' ElseIf Not Intersect(cll, MonthlyRent) Is Nothing _
' And .Formula Is Nothing Then
'
' .FormulaR1C1 = "=IFERROR(RC[2]/12/RC[-9],0)"
'Make a persistent formula for Annual Rent
ElseIf Not Intersect(cll, AnnualRent) Is Nothing _
And .Offset(0, -2).FormulaR1C1 <> "=IFERROR(RC[2]/12/RC[-9],0)" Then
.Offset(0, -2).FormulaR1C1 = "=IFERROR(RC[2]/12/RC[-9],0)"
' 'Reinstate Annual Rent formula when it's deleted
' ElseIf Not Intersect(cll, AnnualRent) Is Nothing _
' And .formula Is Nothing Then
'
' .Formula R1C1 = "=RC[-2]*12*RC[-11]"
End If
End With
Next cll
Application.EnableEvents = True
End Sub
