Excel VBA - 遍历范围并在每个单元格中设置公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6647151/
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 - Loop through range and set formula in each cell
提问by Kenny Bones
I've got a workbook where I have one worksheet which contains a lot of data. My goal is to create a macro that inserts a formula in a separate sheet to copy the data from the first sheet. Lets call the first sheet "Numbers1" and the second sheet "TidyNumbers1".
我有一个工作簿，其中有一个包含大量数据的工作表。我的目标是创建一个宏，在单独的工作表中插入公式以从第一个工作表复制数据。让我们将第一张纸称为“Numbers1”，将第二张纸称为“TidyNumbers1”。
In the sheet "TidyNumbers1" I want to loop through each cell from column A to M and rows 1 to 60. So I've got a macro that so far looks like this:
在工作表“TidyNumbers1”中，我想遍历从 A 列到 M 行和第 1 行到 60 行的每个单元格。所以我有一个宏，到目前为止看起来像这样：
Sub updateFormulasForNamedRange()
Dim row, col, fieldCount As Integer
colCount = 13
RowCount = 60
For col = 1 To colCount
For row = 1 To RowCount
Dim strColCharacter
If col > 26 Then
strColCharacter = Chr(Int((row - 1) / 26) + 64) & Chr(((row - 1) Mod 26) + 65)
Else
strColCharacter = Chr(row + 64)
End If
Worksheets("TidyNumbers1").Cells(row, col).Formula = "=IF(Numbers1!E" & col & "<>0;Numbers1!" & strColCharacter & row & ";"")"
Next row
Next col
End Sub
But the formula is supposed to looks like this for Column A, row 2:
但是对于 A 列第 2 行，公式应该如下所示：
IF(Numbers1!E2<>0;Numbers1!A2;"")"
And the formula in Column A, row 3 should look like this:
A 列第 3 行中的公式应如下所示：
IF(Numbers1!E3<>0;Numbers1!A3;"")"
Formula in Column B, row 2 should look like this:
B 列第 2 行中的公式应如下所示：
IF(Numbers1!E2<>0;Numbers1!B2;"")"
In other words, the formula looks to see if the value in Column E, row % is anything but 0 and copies it if conditions are met.
换句话说，该公式会查看列 E、行 % 中的值是否不是 0，并在满足条件时复制它。
But, I see that I need to translate my integer variable Row with letters, because the formula probably needs "A" instead of 1. Also, I get a 1004 error (Application-defined or object-defined error) if I just try to use:
但是，我看到我需要用字母来翻译我的整数变量 Row，因为公式可能需要“A”而不是 1。此外，如果我只是尝试，我会收到 1004 错误（应用程序定义或对象定义的错误）用：
Worksheets("Numbers1").Cells(row, col).Formula = "=IF(Numbers1!E" & row & "<>0;Numbers1!" & col & row & ";"")"
I clearly see that the integer row should be translated to letters, if that's possible. Or if anyone has any other suggestions that might work. Also, the 1004 error is unclear to me why happens. I can define a string variable and set the exact same value to it, and there's no error. So it's probably the formula bar that whines about it I guess?
我清楚地看到整数行应该被转换为字母，如果可能的话。或者如果有人有任何其他可能有用的建议。此外，1004 错误我不清楚为什么会发生。我可以定义一个字符串变量并为其设置完全相同的值，并且没有错误。所以我猜可能是公式栏在抱怨它？
采纳答案by Doc Brown
Here is a former post of mine containing functions for conversion of column numbers to letters and vice versa:
这是我以前的一篇文章，其中包含将列号转换为字母的函数，反之亦然：
VBA Finding the next column based on an input value
EDIT: to your 1004 error: Try something like this:
编辑：到您的 1004 错误：尝试这样的事情：
=IF(Numbers1!E" & row & "<>0,Numbers1!A" & row & ","""")"
(use ;instead of ,, and ""for onequotation mark in a basic string, """"for twoquotation marks).
（使用;替代,，并""为一个在基本字符串引号，""""为2引号）。
回答by Tomamais
Would not it be easier to get the cell address with the Cells.Address function?
使用 Cells.Address 函数获取单元格地址不是更容易吗？
For example:
例如：
MsgBox Cells(1, 5).Address
MsgBox Cells(1, 5).Address
Shows "$E$1"
显示“$E$1”
Best Regards
此致
