Excel VBA - 在每个单元格中循环遍历范围并设置公式

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错误我不清楚为什么会发生。我可以定义一个字符串变量并为它设置完全相同的值,并且没有错误。所以这可能是公式栏,我想是抱怨它?

2 个解决方案

#1

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

VBA根据输入值查找下一列

EDIT: to your 1004 error: Try something like this:

编辑:你的1004错误:尝试这样的事情:

  =IF(Numbers1!E" & row & "<>0,Numbers1!A" & row & ","""")"

(use ; instead of ,, and "" for one quotation mark in a basic string, """" for two quotation marks).

(使用;而不是,,和“”表示基本字符串中的一个引号,“”“表示两个引号)。

#2

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"

Best Regards

#1