VBA Fill cells on multiple sheets with formulas

Question

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

score 4 · Accepted Answer · 2018-04-15 07:25:47Z

4

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

edited Apr 15, 2018 at 7:25

answered Apr 15, 2018 at 7:09

user4039065

Sign up to request clarification or add additional context in comments.

7 Comments

user4039065 Over a year ago

Personally, I abhor doubling up quotes so I've collected a few alternatives that allows me to avoid them altogether.

QHarr Over a year ago

OP: 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.

lydias Over a year ago

@Jeeped I tried to use text(,), Excel asks me to import a file whenever the formula is used...

user4039065 Over a year ago

There are many responses; some I mark as duplicates. It depends on the quality of the question and the poster whether I provide a direct response.

user4039065 Over a year ago

@lydias - That really shouldn't happen; even on a regional language other than EN-US as vba is very EN-US-Centric.

|

QHarr · Accepted Answer · 2018-04-15 07:29:09Z

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

paul bica · Accepted Answer · 2018-04-22 15:55:20Z

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

Collectives™ on Stack Overflow

VBA Fill cells on multiple sheets with formulas

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related