VBA用公式填充多张纸上的单元格 [英] VBA Fill cells on multiple sheets with formulas

问题描述

我正在尝试为工作簿的每张纸上的每个单元格设置公式.我需要在2个不同范围的单元格中分布2个公式.我在Variant中定义了strFormula 1和strFormula 2.

I'm trying to set formulas for each cells in every sheet of a workbook. There are 2 formulas that I need to distribute across 2 different ranges of cells. I defined strFormula 1 and 2 in a Variant.

对于公式1，我希望将其分配到A到AJ列下的单元格

For formula 1 I hope to distribute to cells under column A to AJ

对于公式2，我希望将其分配到AK列到AR列下

For formula 2 I hope to distribute to cells under column AK to AR

运行循环后，收到以下错误，并在定义strFormulas(2)时收到语法错误.希望对解决这些问题有所帮助.

I received following error after running the loop and a syntax error when defining strFormulas(2). Would like some help on fixing those problems.

谢谢！

运行时错误1004，应用程序定义的错误或对象定义的错误

Run time error 1004, Application-defined or object-defined error

 Dim w As Long
 Dim strFormulas(1 To 2) As Variant

 For w = 2 To ActiveWorkbook.Worksheets.Count
    With ActiveWorkbook.Worksheets(w)

        strFormulas(1) = "=IF(ISBLANK('Sheet 1'!A4),"",'Sheet 1'!A4)"
        'strFormulas(2) has a syntax error.
        strFormulas(2) = "=IF('Sheet 2'!N1838="S","S","")"

        .Range("A2:AJ2").Formula = strFormulas(1)
        .Range("A2:AJ2000").FillDown
        .Range("AK2:AR2").Formula = strFormulas(2)
        .Range("AK2:AR2000").FillDown

    End With
Next w

推荐答案

带引号的字符串中的引号需要加倍. ""的替代项是text(，)，因此您不需要"""". S是ASCII 83.

Quotes in a quoted string need to be doubled up. An Alternate to "" is text(,) so you don't need """". An S is ascii 83.

strFormulas(1) = "=IF(ISBLANK('Sheet 1'!A4),"""",'Sheet 1'!B4)"
strFormulas(1) = "=IF(ISBLANK('Sheet 1'!A4), text(,), 'Sheet 1'!B4)"

strFormulas(2) = "=IF('Sheet 2'!N1838=""S"",""S"","""")"
strFormulas(2) = "=IF('Sheet 2'!N1838=char(83), char(83), text(,))"

这篇关于VBA用公式填充多张纸上的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！