Excel VBA - 遍历范围并在每个单元格中设置公式 [英] Excel VBA - Loop through range and set formula in each cell

问题描述

我有一个工作簿，其中有一个包含大量数据的工作表.我的目标是创建一个宏，在单独的工作表中插入公式以复制第一张工作表中的数据.让我们将第一张纸称为Numbers1"，将第二张纸称为TidyNumbers1".

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".

在工作表TidyNumbers1"中，我想遍历从 A 列到 M 行以及第 1 行到 60 行的每个单元格.所以我有一个到目前为止看起来像这样的宏:

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:

   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

但是对于 A 列第 2 行，公式应该是这样的:

But the formula is supposed to looks like this for Column A, row 2:

IF(Numbers1!E2<>0;Numbers1!A2;"")"

A 列第 3 行中的公式应如下所示:

And the formula in Column A, row 3 should look like this:

IF(Numbers1!E3<>0;Numbers1!A3;"")"

B 列第 2 行中的公式应如下所示:

Formula in Column B, row 2 should look like this:

IF(Numbers1!E2<>0;Numbers1!B2;"")"

换句话说，该公式会查看列 E、行 % 中的值是否为 0 以外的任何值，并在满足条件时复制它.

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.

但是，我发现我需要用字母转换整数变量 Row，因为公式可能需要A"而不是 1.此外，如果我收到 1004 错误(应用程序定义或对象定义的错误)尝试使用:

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:

Worksheets("Numbers1").Cells(row, col).Formula = "=IF(Numbers1!E" & row & "<>0;Numbers1!" & col & row & ";"")"

我清楚地看到应该将整数行转换为字母，如果可能的话.或者如果有人有任何其他可能有用的建议.此外，1004 错误我不清楚为什么会发生.我可以定义一个字符串变量并为其设置完全相同的值，并且没有错误.所以我猜可能是公式栏在抱怨它?

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?

推荐答案

这是我以前的一篇帖子，其中包含列号与字母之间的相互转换函数:

Here is a former post of mine containing functions for conversion of column numbers to letters and vice versa:

VBA 查找下一列基于输入值

到您的 1004 错误:尝试这样的事情:

to your 1004 error: Try something like this:

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

(使用;代替、和""作为基本字符串中的一个引号，"""" 表示两个引号).

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

这篇关于Excel VBA - 遍历范围并在每个单元格中设置公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！