上下文
我试着做一个无麻烦的多家庭租金分析工作表。最终用户对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
发布于 2018-04-19 17:27:46
更好的方法(因为您已经在使用事件处理程序来响应更改)可能是完全跳过公式,而不是当用户直接更改每月或每年的金额时,只需更新该行上的另一个单元格即可。
或者,如果您想继续使用当前的方法:
而不是
... And .Formula Is Nothing Then
使用
... And Len(.Formula) = 0 Then
“公式”只在单元格没有公式时返回空字符串。
https://stackoverflow.com/questions/49910408
复制相似问题