具有变量的Excel VBA公式 [英] Excel VBA formula with variables

问题描述

我想在我的代码中生成一个公式，例如

I would like to generate a formula in my code, say for example,

 Range("L2:L17).Formula="=SUMIF($A$2:$A$17,A2,G2:G17)"

除此之外，我希望L，G和A是由宏中较早一些其他因素决定的变量，同样，我也希望将2和17替换为两个变量，其具体值先于代码。

Except, I would like L, G, and A to be variables that are decided by some other factors earlier in the macro. Similarly, I would also like 2, and 17 to be replaced by two variables, whose specific values are determined earlier in the code.

所以像

Range("var1 j: var1 k).Formula="=SUMIF($ var2 $ j : $ var2 $ k, var2 j, var3 j : var3 k)"

我想我可以将这些变量转换为字符串，只是将字符串连接起来，但是我还没有做到这一点。我尝试的一切都会给出错误。

I guess I could convert these variables to string and just to a string concatenate but I am not able to do it just yet. Everything I try gives errors.

Range(foldtot_add & "2:"& foldtot_add & FinalRow).Formula="=SUMIF("$"&FolderId_add & "$2:" & "$"& FolderId_add & FinalRow, FolderId_add &" 2", daycountcol2 & "2 :" & daycountcol2 & FinalRow)"

其中，foldtot_add，FolderId_add和daycountcol2是我的列的名称，FinalRow是一行的名称。 / p>

Where foldtot_add, FolderId_add, and daycountcol2 are names of my columns and FinalRow is the name of a row.

推荐答案

如果您还需要列的变量，您可以使用行号和列号引用单元格;然后，获取地址并填充你的公式。下面的示例设置行开始，行结束和列号，然后获取每个范围的地址

In the case you need variables for columns too, you could reference the cell(s) using the row and column number; then, get the address and populate that in your formula. The example below sets the row start, row end, and the column numbers, and then gets the address for each range

Dim rowStart as Integer
Dim rowEnd as Integer
Dim firstCol as Integer
Dim secondCol as Integer
Dim thirdCol as Integer
Dim aAddress as String
Dim gAddress as String
Dim lAddress as String
Dim aCell as String


rowStart = 2
rowEnd = 17
firstCol = 1
secondCol = 7
thirdCol = 12

' This gives you "$A$2:$A$17"
aAddress = Cells(rowStart, firstCol).Address & ":" & _
                Cells(rowEnd, firstCol).Address

' This gives you "G2:G17"
gAddress = Cells(rowStart, secondCol).Address & ":" & _
                Cells(rowEnd, secondCol).Address(RowAbsolute:=False, _
                                                 ColumnAbsolute:=False)

' This gives you "L2:L17"
lAddress = Cells(rowStart, thirdCol).Address & ":" & _
                Cells(rowEnd, thirdCol).Address(RowAbsolute:=False, _
                                                 ColumnAbsolute:=False)

' This gives you "A2"
aCell = Cells(rowStart, firstCol).Address(RowAbsolute:=False, _
                                                 ColumnAbsolute:=False)

' Build the formula to insert in the cells
Range(lAddress).Formula = "=SUMIF(" & aAddress & "," & _
    aCell & "," & gAddress & ")"

从这里，您可以编写一个循环或其他增量代码，以便根据需要更改行/列号变量。

From here, you can write a loop or some other incremental code to alter the row/column number variables as you see fit.

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