VBA更改复杂公式 [英] VBA changes complex formula

问题描述

我将代码与单选按钮一起使用(请参阅最后一个代码)将新行插入第15行，这会将旧数据向下推，同时将相同的公式复制到曾经存在的第15行中(在该行中) 15)单击该按钮之前.

I use code with Radio Button (see last code) to insert a new row into row 15, which pushes the old data down, and at the same time, copies the same formulas into row 15 that were once there (in row 15) before the button is clicked.

单击按钮后，我可以在E15中输入3位数据，然后按Enter键，并且所有公式都将基于E15中输入的内容在第15行中进行计算.

Once the button is clicked, I can enter 3 digit data into E15, then hit enter, and all formulas calculate across row15, based on what is entered in E15.

现在我喜欢代码的执行方式，但是由于某些原因，CG15中的公式从

Now I love how the code performs, but for some reason, the formula in CG15 changes from

=IF(CF15=1,IF(CF15<>"",COUNTBLANK(INDEX(CF14:CF$14,MATCH(9.99999999999999E+307,CF14:CF$14)):CF15),""),"")

到

=IF(CF15=1,IF(CF15<>"",COUNTBLANK(INDEX(CF13:CF$14,MATCH(9.99999999999999E+307,CF13:CF$14)):CF15),""),"")

我不知道问题是否出在CG15的代码或公式中 这是一个测试"文件，下面是代码

I can't figure out if the issue is in the code or formula in CG15 Here's a "test" file and below is the code

测试表

Sub AddNewDrawing()
'
' AddNewDrawing Macro
'

'
    Rows("15:15").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A16:ua16").Select
    Selection.AutoFill Destination:=Range("A15:ua16"), Type:=xlFillDefault
    Range("A15:ua16").Select
    Range("E15").Select
    Selection.ClearContents
End Sub

这可能是简单的解决方法，但我很困惑.我需要公式保持不变且不变.

This might be simple fix but I am stumped. I need the formula to remain the same and not change.

关于每个代码的作用，这里有更深入的信息. CF15列具有该公式，如果满足条件，则返回1.

Here is more in depth information as to what each code does. Column CF15 has this formula which returns 1 if the condition is met.

=IF(COUNTIF($AW16:$CC18,O15),1,IF(COUNTIF($AW16:$CC18,P15),1,IF(COUNTIF($AW16:$CC18,Q15),1,"")))

CG15列具有此公式，用于计算CF15列中每个1之间的跳跃(空白单元格). 注意:CG $ 14有一个永久性0(零)，用作计数开始时的占位符.

Column CG15 has this formula which counts skips(blank cells) between each of the 1's in column CF15. Note: CG$14 has a permanent 0(zero) which acts as a place holder for the beginning of the count.

=IF(CF15=1,IF(CF15<>"",COUNTBLANK(INDEX(CF14:CF$14,MATCH(9.99999999999999E+307,CF14:CF$14)):CF15),""),"")

谢谢

推荐答案

当您插入新的第15行时，CG15中的公式将下推至CG16，并且公式中的相对地址引用第15行(例如CF15)成为第16行(例如CF16).

When you insert a new row 15, the formula in CG15 is pushed down to CG16 and the relative addresses in the formula that refer to row 15 (e.g. CF15) become row 16 (e.g. CF16).

但是，小于15的相对地址(如CF14)不受行插入的影响.当您将公式从CG16回填"到CG15时，它们都会受到影响，因此CF14:CF $ 14变为CF13:CF $ 14，就像CF16还原为原始CF15一样.

However, the relative addresses that are less than 15 like CF14 are unaffected by the row insertion. When you 'backfill' the formula from CG16 to CG15 they are all affected so CF14:CF$14 becomes CF13:CF$14 just as CF16 reverts back to the original CF15.

将公式更改为不受行插入或回填之间的相对差异影响的内容.

Change the formula to something that isn't affected by the relative difference between row insertion or backfill.

相对行CF14可以用CG15编写为

A row relative CF14 can be written in CG15 as,

index(CF:CF, row()-1)

这就是您的公式，

=IF(CF15=1, COUNTBLANK(INDEX(CF$14:index(CF:CF, row()-1), MATCH(1e99, CF$14:index(CF:CF, row()-1))):CF15), text(,))

这篇关于VBA更改复杂公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！