Excel VBA将单元格值设置为公式。

Hey Everyone I have some hard code in excel vba that will set a given cells value = to a formula and i have multiple lines of code that does this for each cells value but changes the formula to look at the specific column its under.

嘿，我在excel vba中有一些硬编码，它会设置一个给定的单元格值=一个公式，我有多个代码行，为每个单元格值做这个，但是改变公式来查看它下面的特定列。

So my question is, is there any way to code the stuff below in 1 line so that the formula will automatically change the columns its looking at depending on the column its actually in.

所以我的问题是，是否有办法将这些东西编码成1行，这样公式就会自动地改变列向量的形式取决于它的列向量。

Sheets("Standalone Non Clustered").Range("G10").Formula = "=CEILING(Worksheet!$G$9*(1+Worksheet!$G$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("H10").Formula = "=CEILING(Worksheet!$H$9*(1+Worksheet!$H$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("I10").Formula = "=CEILING(Worksheet!$I$9*(1+Worksheet!$I$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("J10").Formula = "=CEILING(Worksheet!$J$9*(1+Worksheet!$J$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("K10").Formula = "=CEILING(Worksheet!$K$9*(1+Worksheet!$K$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("L10").Formula = "=CEILING(Worksheet!$L$9*(1+Worksheet!$L$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("M10").Formula = "=CEILING(Worksheet!$M$9*(1+Worksheet!$M$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("N10").Formula = "=CEILING(Worksheet!$N$9*(1+Worksheet!$N$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("O10").Formula = "=CEILING(Worksheet!$O$9*(1+Worksheet!$O$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("P10").Formula = "=CEILING(Worksheet!$P$9*(1+Worksheet!$P$10+Info!$B$14),5)"

See how each range goes from G to P in the alphabet and the formula in each line is adjusted for that G to P column. Is there a way to have one line for Range("G10:P10") and have the formula change itself based on what column its actually in on the spreadsheet?

在字母表中，每一个范围从G到P，每一行的公式都经过G到P列的调整。是否有一种方法可以有一个范围(“G10:P10”)，并根据它实际在电子表格中的哪个列来改变它本身?

Edit:

编辑:

Removing the $ syntax fixed the problem but I am coming up with another problem:

删除$语法解决了这个问题，但是我又遇到了另一个问题:

Say I have the range set to a variable and I want to add a row to that range variable and use that variable in the formula line above. How would i go about incrementing that variable by 1.

假设我有一个变量的范围，我想在这个范围变量中添加一行并在上面的公式行中使用那个变量。我怎么把这个变量增加1。

        Set shownDatabaseRows = Sheets("Standalone Non Clustered").Range("10:10")
Set hiddenDatabaseRows = Sheets("Standalone Non Clustered").Range("11:108")

hiddenDatabaseRows.EntireRow.Hidden = True
shownDatabaseRows.EntireRow.Hidden = False

    Sheets("Standalone Non Clustered").shownDatabaseRows.Formula = "=CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5)"


For Each cell In rng

    If cell.Value >= 2048 Then


        shownDatabaseRows = shownDatabaseRows.Count + 1

        shownDatabaseRows.EntireRow.Hidden = False

                Sheets("Standalone Non Clustered").shownDatabaseRows.Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/2"

      ...

I am getting an error at the formula lines so im thinking that something is wrong with the way I am setting up the shownDatabaseRows variable. any ideas?

我在公式行中出错，所以我认为我设置shownDatabaseRows变量的方式有问题。什么好主意吗?

3 个解决方案

#1

Sheets("Standalone Non Clustered").Range("G10:P10").Formula = "=CEILING(Worksheet!R9C[0]*(1+Worksheet!R10C[0]+Info!$B$14),5)"

表(“独立非集群”).Range(G10:P10)。公式= " =上限(工作表! R9C[0]*(1 +工作表! R10C[0]+信息! B 14美元),5)”

#2

You can always set the formula for a range such as G10:P10 in your case

您总是可以设置一个范围的公式，比如G10:P10。

Sheets("Standalone Non Clustered").Range("G10:P10").Formula = "=CEILING(Worksheet!G9*(1+Worksheet!G10+Info!$B$14),5)"

just remove the $ from the cell references which should be dynamically changed.

只需从需要动态更改的单元格引用中删除$。

Also you can follow Set formula to a range of cells for further clarification.

你也可以按照设定的公式到一系列的细胞中进一步的澄清。

#3

Something like....

类似....

Sheets("Standalone Non Clustered").Range("G10").Formula = "=CEILING(Worksheet!$G$9*(1+Worksheet!$G$10+Info!$B$14),5)"
Sheets("Standalone Non Clustered").Range("G10:P10").FillRight

#1