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

问题描述

我有一本工作簿，其中有一个包含大量数据的工作表。
我的目标是创建一个宏，将一个公式插入到单独的工作表中以从第一个工作表复制数据。让我们调用第一张表Numbers1和第二张表TidyNumbers1。

在TidyNumbers1的表格中，我想循环从列A到M的每个单元格，行1到60.所以我有一个到目前为止这样的宏：

  Sub updateFormulasForNamedRange（）
 Dim row，col，fieldCount As Integer 
 colCount = 13 
 RowCount = 60 
 
对于col = 1到colCount 
对于row = 1到RowCount 
 Dim strColCharacter 
 
如果col> 26然后
 strColCharacter = Chr（Int（（row-1）/ 26）+ 64）& Chr（（（row-1）Mod 26）+ 65）
 Else 
 strColCharacter = Chr（row + 64）
 End If 
 
工作表（TidyNumbers1 ）.Cells（row，col）.Formula == IF（Numbers1！E& col&0; Numbers1！& strColCharacter& row&;）
下一行
下一个col 
 
 End Sub 
  

但是该公式应该像A列，第2行：

  IF（Numbers1！E2<> 0; Numbers1 ！A2;）
  

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

  IF（Numbers1！E3<> 0; Numbers1！A3;）
  

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

  IF（Numbers1！E2<> 0; Numbers1！B2;）
  

$ b $换句话说，公式看看列E列％的值是否为0，如果条件ar e见面

但是，我看到我需要用字母翻译我的整数变量Row，因为公式可能需要A而不是1.而且，我得到一个1004错误（应用程序定义或对象定义的错误），如果我只是尝试使用：

 工作表（Numbers1）。 row，col）.Formula == IF（Numbers1！E& row&0; Numbers1！& col& row&;）
  

我清楚地看到整数行应该翻译成字母，如果可能的话。或者如果有任何其他建议可能有效。此外，1004错误对我来说不清楚为什么会发生。我可以定义一个字符串变量并设置完全相同的值，并且没有错误。所以这可能是我猜想的公式栏吧？

解决方案

这是一个以前的职位，列号到字母，反之亦然：

VBA根据输入值查找下一列

编辑：到您的1004错误：尝试这样的事情：

  = IF（Numbers1！E& row&& ！a&，）
  

（使用; 而不是，和 / em>引号在为两个引号）。

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

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

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

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

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

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

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

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.

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 & ";"")"

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 Finding the next column based on an input value

EDIT: 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屋！