Excel VBA 将单元格值设置为公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17787564/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA setting cell value to a formula
提问by user1305569
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*(1+Worksheet!$G+Info!$B),5)"
Sheets("Standalone Non Clustered").Range("H10").Formula = "=CEILING(Worksheet!$H*(1+Worksheet!$H+Info!$B),5)"
Sheets("Standalone Non Clustered").Range("I10").Formula = "=CEILING(Worksheet!$I*(1+Worksheet!$I+Info!$B),5)"
Sheets("Standalone Non Clustered").Range("J10").Formula = "=CEILING(Worksheet!$J*(1+Worksheet!$J+Info!$B),5)"
Sheets("Standalone Non Clustered").Range("K10").Formula = "=CEILING(Worksheet!$K*(1+Worksheet!$K+Info!$B),5)"
Sheets("Standalone Non Clustered").Range("L10").Formula = "=CEILING(Worksheet!$L*(1+Worksheet!$L+Info!$B),5)"
Sheets("Standalone Non Clustered").Range("M10").Formula = "=CEILING(Worksheet!$M*(1+Worksheet!$M+Info!$B),5)"
Sheets("Standalone Non Clustered").Range("N10").Formula = "=CEILING(Worksheet!$N*(1+Worksheet!$N+Info!$B),5)"
Sheets("Standalone Non Clustered").Range("O10").Formula = "=CEILING(Worksheet!$O*(1+Worksheet!$O+Info!$B),5)"
Sheets("Standalone Non Clustered").Range("P10").Formula = "=CEILING(Worksheet!$P*(1+Worksheet!$P+Info!$B),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 列调整每行中的公式。有没有办法让 Range("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*(1+Worksheet!G+Info!$B),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*(1+Worksheet!G+Info!$B),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?
我在公式行中遇到错误，所以我认为我设置 ShowDatabaseRows 变量的方式有问题。有任何想法吗？
回答by LS_???
Sheets("Standalone Non Clustered").Range("G10:P10").Formula = "=CEILING(Worksheet!R9C[0]*(1+Worksheet!R10C[0]+Info!$B$14),5)"
Sheets("Standalone Non Clustered").Range("G10:P10").Formula = "=CEILING(Worksheet!R9C[0]*(1+Worksheet!R10C[0]+Info!$B$14),5)"
回答by AKS
You can always set the formula for a range such as G10:P10in your case
您始终可以设置范围的公式，例如G10:P10在您的情况下
Sheets("Standalone Non Clustered").Range("G10:P10").Formula = "=CEILING(Worksheet!G9*(1+Worksheet!G10+Info!$B),5)"
just remove the $from the cell references which should be dynamically changed.
只需$从应该动态更改的单元格引用中删除。
Also you can follow Set formula to a range of cellsfor further clarification.
您也可以按照将公式设置为一系列单元格进行进一步说明。
回答by Vasim
Something like....
就像是....
Sheets("Standalone Non Clustered").Range("G10").Formula = "=CEILING(Worksheet!$G*(1+Worksheet!$G+Info!$B),5)"
Sheets("Standalone Non Clustered").Range("G10:P10").FillRight
