Контекст

Я пытаюсь сделать простой лист анализа аренды многоквартирных домов. Конечный пользователь ничего не знает об Excel. Точками данных одного раздела являются типы квартир, количество квартир каждого типа, ежемесячная арендная плата за единицу и годовая арендная плата за единицу< /эм>. Я хотел бы, чтобы пользователь мог ввести ежемесячную арендную плату и рассчитать годовую арендную плату или наоборот (при условии, что в обоих случаях они также ввели < em>количество единиц каждого типа). Рассматриваемые формулы проще, чем вы могли себе представить:

Ежемесячная арендная плата = Годовая арендная плата / 12 / Количество квартир

Годовая арендная плата = Ежемесячная арендная плата * Количество квартир * 12

Я включил итеративный расчет, чтобы сохранить формулу в каждом столбце арендной платы, потому что они полагаются друг на друга при расчете (зависимые формулы). Это прекрасно работает, но только для первой записи. Если пользователь ввел данные ежемесячной арендной платы, но вместо этого хотел бы ввести годовые данные, формула для ежемесячного расчета исчезнет. Однако мне удалось решить эту проблему с помощью хитрого кода в Private Sub Worksheet_Change(ByVal Target As Range), который я вставил ниже. Это работает отлично. Всякий раз, когда изменяется одна формула, формула вставляется в другую, перезаписывая ввод пользователя -- если только... одна из формул не была изменена, а удалена.

Проблема

Я не могу найти информацию о том, как вставить формулу, когда пользователь ее удаляет. Например: предположим, что пользователь ранее ввел информацию о ежемесячной арендной плате, и рабочий лист автоматически рассчитал годовую арендную плату. Теперь, когда пользователь возвращается, чтобы удалить ежемесячную арендную плату, ячейка месячной арендной платы не сбрасывается, она пуста. Формулы больше нет, и когда они редактируют годовую арендную плату, VBA не восстанавливает формулу месячной арендной платы.

Я не знаю:

• почему закомментированный блок ElseIf не работает

• почему иногда удаление информации, введенной в одну из зависимых ячеек, очищает другую, а иногда нет

• почему удаление формулы из одного и редактирование другого не восстанавливает удаленную формулу. Например. удаление ежемесячной арендной платы, а затем редактирование годовой арендной платы не воссоздает формулу ежемесячной арендной платы

Идеальным поведением, когда пользователь удаляет одну из зависимых формул, было бы возвращение ячеек в исходное положение с обеими круговыми формулами на месте. Кажется, что просто повторный ввод формулы при ее удалении должен помочь, но, что удивительно, я не могу найти никакой помощи о том, как это сделать.

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