在这种情况下，如何避免使用VBA进行循环引用? [英] How to avoid a circular reference with VBA in this case?

问题描述

我要输入很多行数据.有时我会在A列中输入净额，有时我会在C列中输入毛额.净额+增值税=毛额.在所有情况下，我都希望公式自动为我计算丢失的数字.

I'm entering many rows of data. Sometimes I'll enter net in column A, and sometimes I'll gross in column C. Net + VAT = Gross. In all cases I want the formulas to calculate the missing numbers for me automatically.

我有三栏(ABC)列A代表净额，B代表名义增值税，C总额.我在B中有一个公式，如果是A增值税，或者如果是B VAT则为空白.

I have three columns (ABC) column A represents Net, B represents nominal VAT, and C Gross. I have a formula in B that if A VAT, or if B VAT else blank.

当前，如果我在A中放入任何内容，则会计算增值税，C为空白:

Currently, if I put anything in A, the VAT is calculated and C is blank:

  A    B    C
  100  20  

同样，如果我在C中放入任何内容，则计算增值税，但A为空白:

And equally, if I put anything in C, the VAT is calcualted but A is blank:

A    B    C
    20    120

下面是我想要的，无论是给定的A还是C.

What I want is below, whether it is A that is given, or C.

A    B    C
100  20   120

我正在尝试计算净给定总值，以及总给定净值在一行上.

I am trying to Calculate Net given gross figure, and gross given net figure on a single row.

请注意，B列受保护，我有一个公式可以自动计算增值税，无论是先输入数字还是先输入数字:

Note, column B is protected, I have a formula that calculates the VAT automatically whether a figure is entered into C first, or A first:

=IF(E2>0,E2*VATRATE,IF(G2>0,G2-(G2/(1+VATRATE)),""))

理想的情况是使用VBA，如果A给定了cal C，并且C给定了calc A.

Ideally with VBA, if A given then cal C, and if C given calc A.

推荐答案

实际上，您根本不需要VBA.使用以下公式:

Actually you don't need VBA at all for this. Use these formulas:

• A2中的网络 = IF(NOT(ISFORMULA(C2))，C2-B2,0)
B2中的
• 增值税 = IF(NOT(ISFORMULA(A2))，A2 * VATRATE，IF(NOT(ISFORMULA(C2))，C2-(C2/(1 + VATRATE))，0)))
• C2中的毛额 = A2 + B2

诀窍是确定是否手动输入了数字.一旦知道了这一点，就可以使用if语句来避免循环引用.

The trick is to determine whether a number was manually entered. Once you know that, you can use if statements to avoid a circular reference.

根据需要向下复制尽可能多的行.

Copy down for as many lines as you need.

这篇关于在这种情况下，如何避免使用VBA进行循环引用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！