需要帮助excel macro- vba [英] Need assistance with excel macro- vba
问题描述
有一组 SUMIF
公式,我需要经常调整&我唯一需要调整的是它的第三部分。每次运行宏时,第3部分或总和范围的列需要向右移1列。
have a set of SUMIF
formulas that I need to adjust frequently & the only thing I need to adjust is the 3rd part of it. The 3rd part, or sum range, columns need to be shifted over to the right by 1 column each time the macro is run.
例如,我的公式可以是:
= SUMIF($ A $ 1:$ A $ 10,$ A15,!$ C $ 1:$ C $ 10)
For example, my formula can be:
=SUMIF($A$1:$A$10,$A15,!$C$1:$C$10)
- 我希望能够运行一个调整上述
公式的宏,以便不从添加列C1:C10
,它会添加
列D1:10
。 - 然后下一次运行宏,公式将再次更改为E列,并依此类推。
- I would like to be able to run a macro that would adjust the above
formula so that instead of adding from
Column C1:C10
, it would addColumn D1:10
. - Then the next time I run the macro, the formula will change again to Column E and so-on.
然后,该公式将被拖放到下面所有必需的行唯一改变的是第二部分的行#。
This formula would then be dragged down to all necessary rows below with the only thing changing is the 2nd part's row #.
有没有一个简单的方法来实现这一点?
Is there a simple way to accomplish this?
感谢提前!
这是从记录更改第三部分的宏获得的VB代码,但是到目前为止,它只会将其更改为特定的列每当我运行它。
Here is the VB code that I got from recording a macro that changes the 3rd part, but as of now it will only change it to that specific column whenever I run it.
ActiveCell .FormulaR1C1 == SUMIF('WorksheetA'!R5C1:R159C1,RC1,'WorksheetA'!RC13:R159C13)
推荐答案
此代码存储在公共变量 LngCNt
中运行的次数。请注意,每次打开文件时,计数将设置为0
This code stores the number of times it has been run in a public variable LngCNt
. Note that the count will be set to 0 each time the file opens
LngCnt
变量用于增加公式即
第一次运行= SUMIF($ A $ 1:$ A $ 10,$ A15,$ C $ 1:$ C $ 10) >
second run = SUMIF($ A $ 1:$ A $ 10,$ A15,$ D $ 1:$ D $ 10)
第三次运行= SUMIF($ A $ 1:$ A $ 10,$ A15,$ E $ 1:$ E $ 10)
first run =SUMIF($A$1:$A$10,$A15,$C$1:$C$10)
second run =SUMIF($A$1:$A$10,$A15,$D$1:$D$10)
third run =SUMIF($A$1:$A$10,$A15,$E$1:$E$10)
Public LngCnt As Long
Sub Macro2()
ActiveCell.FormulaR1C1 = "=SUMIF(R1C1:R10C1,R[4]C1,R1C" & 3 + LngCnt & ":R10C" & 3 + LngCnt & ")"
LngCnt = LngCnt + 1
End Sub
这篇关于需要帮助excel macro- vba的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!