以类似的模式将多个功能捆绑在一起

I am making a budget and whatnot in excel. I want to highlight a bunch of side by side rows, and say "for each of these rows on the left, make the row on the right equal to 12 * the left row", the left is the month cost, the right is the year.

我正在制定预算和诸如此类的东西。我想突出显示一堆并排的行,并说“对于左边的每一行,使右边的行等于12 *左行”,左边是月成本,右边是年。

I want the left side to change if I change the right, and the right to change if I change the left. I also want me other functions (the sums of these rows vertically) to not get damaged.

如果我改变了右侧,我希望左侧改变,如果改变左侧,我希望改变右侧。我也希望我的其他功能(垂直这些行的总和)不会被损坏。

Is this possible en masse, where I can highlight 20 or more rows vertically and tell it the pattern? Thank you

这可能是集体,我可以垂直突出显示20行或更多行并告诉它模式?谢谢

1 个解决方案

#1

I know you stated you don't know VBA, and since it's fairly simple if you do know it, I thought I would help you out.

我知道你说过你不懂VBA,如果你知道它就很简单,我想我会帮你的。

Place this inside the Worksheet Module in the VBE for the sheet where the budge information is. Here is a tutorial on how to place the code in the worksheet module.

将其放在VBE中的工作表模块内,以获取预算信息所在的工作表。这是一个关于如何将代码放在工作表模块中的教程。

The only thing you may need to adjust is the range references (F3:F23 and G3:G23) to your actual cell references.

您可能需要调整的唯一事项是您的实际单元格引用的范围引用(F3:F23和G3:G23)。

Private Sub Worksheet_Change(ByVal Target As Range)

'if monthly data changes
If Not Intersect(Target, Me.Range("F3:F23")) Is Nothing Then

    Application.EnableEvents = False
    Target.Offset(, 1).Value = Target * 12
    Application.EnableEvents = True

End If

'if yearly data changes
If Not Intersect(Target, Me.Range("G3:G23")) Is Nothing Then

    Application.EnableEvents = False
    Target.Offset(, -1).Value = Target / 12
    Application.EnableEvents = True

End If

End Sub

#1