首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >删除后的重新状态公式

删除后的重新状态公式
EN

Stack Overflow用户
提问于 2018-04-18 23:20:01
回答 1查看 57关注 0票数 1

上下文

我试着做一个无麻烦的多家庭租金分析工作表。最终用户对excel一无所知。一个部分的数据点是单元类型、每种类型的单元数量、每个单元的月租金和单位的年租金。我希望用户能够输入每月租金,并将年租金计算出来,反之亦然(假设,在这两种情况下,他们也输入了每种类型的单位数量)。这些公式比你想象的要简单得多:

月租=年租/ 12 /单位数目

年租金=每月租金*单位数目* 12

我启用了迭代计算,允许我在每个租金列中保留一个公式,因为它们在计算时相互依赖(相关公式)。这很好,但只适用于第一个条目。如果用户输入了每月租金数据,但现在想要输入年度数据,则每月计算公式就消失了。然而, --我能够用下面粘贴的一些巧妙的Private Sub Worksheet_Change(ByVal Target As Range)代码来解决这个问题。这工作得很好。当一项更改时,将为另一项插入公式,覆盖用户输入--除非.其中一个公式被删除,而不是刚刚更改。

问题

当用户删除公式时,我找不到关于如何插入公式的任何信息。例如:假设用户以前输入了月租信息,工作表自动计算年租金。现在,当用户返回删除月租金时,月租金单元格不会被重置,它是空的。公式不再存在,当他们编辑年度租金时,VBA不会恢复每月租金公式。

我不知道:

  • 注释掉的ElseIf块为什么不能工作
  • 为什么有时删除输入到一个依赖单元中的信息会清除另一个依赖的单元格,而有时却不会
  • 为什么从一个公式中删除公式,然后编辑另一个公式,并不会恢复已删除的公式。例如,删除月租,然后编辑年租金并不会重新创建月租公式

当用户删除其中一个依赖公式时,理想的行为是使单元格返回到它们的起始位置,同时两个循环公式都已就位。它似乎只是简单地重新输入公式时,它被删除,应该会发挥作用,但令人惊讶的是,我找不到任何帮助,如何做到这一点。

代码语言:javascript
运行
复制
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
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2018-04-19 17:27:46

更好的方法(因为您已经在使用事件处理程序来响应更改)可能是完全跳过公式,而不是当用户直接更改每月或每年的金额时,只需更新该行上的另一个单元格即可。

或者,如果您想继续使用当前的方法:

而不是

代码语言:javascript
运行
复制
 ... And .Formula Is Nothing Then 

使用

代码语言:javascript
运行
复制
 ... And Len(.Formula) = 0 Then 

“公式”只在单元格没有公式时返回空字符串。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/49910408

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档