VBA Fill cells on multiple sheets with formulas

问题:

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.

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

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

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

Thank you!

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

回答1:

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(,))"

回答2:

1) Double up on your "". As @Jeeped has stated.

2) But be careful. This relies on the ordering of your sheets e.g. If Sheet 1 is not the first sheet you will end up putting formulas in it referencing Sheet 1; which may, or may not, be what you want.

Option Explicit

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

    For w = 2 To ActiveWorkbook.Worksheets.Count
        With ActiveWorkbook.Worksheets(w) 'this targets by worksheet order so be careful
         strFormulas(1) = "=IF(ISBLANK('Sheet 1'!A4),"""",'Sheet 1'!B4)"
            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
End Sub

回答3:

Another way to fill the formulas on all sheets at the same time

---

Option Explicit

Public Sub FillFormulasAcrossSheets()
    Dim i As Long, wsArr As Variant

    With ActiveWorkbook.Worksheets  'make array with all sheets
        ReDim wsArr(3 To .Count)
        For i = 3 To .Count
            wsArr(i) = i
        Next
    End With

    Application.ScreenUpdating = False

        Sheets(wsArr).Select
        'Or: ActiveWorkbook.Worksheets.Select       'Selects all
        'Or: Sheets(Array(2, 3, "Sheet5")).Select   'Specific sheets

        Range("A2:AJ2000").Select
                 Selection.Formula = "=IF('Sheet2'!N1838=""S"",""S"","""")"
        Range("AK2:AR2000").Select
                 Selection.Formula = "=IF('Sheet2'!N1838=char(83), char(83), text(,))"

        Range("A1").Activate
        Sheets(3).Select

    Application.ScreenUpdating = True
End Sub