How can I apply a series of formulas across an entire workbook/file

Question

I am pretty new to VBA coding and currently I am working with several files that I merge into one workbook. I want to apply the formulas to every worksheet all together since they also belong in the same cell. When I try to run the code I currently have it is giving me this problem: here

I am not sure why it is adding: < ' > to the cells I'm indicating and therefore not letting the formulas to run smoothly. This is the code I have at the moment:

Sub InstacartOld()
    Sheets.Select
    Range("AD805").Activate
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,(AD2:AD803))"

    Sheets.Select
    Range("AD806").Activate
    ActiveCell.FormulaR1C1 = "=SUMIF(AE2:AE803," + ",AD2:AD803)/100"

    Sheets.Select
    Range("AD807").Activate
    ActiveCell.FormulaR1C1 = "=SUMIF(AE2:AE803," - ",AD2:AD803)/100"

    Sheets.Select
    Range("AG805").Activate
    ActiveCell.FormulaR1C1 = "=SUM(AG2:AG803)"

    Sheets.Select
    Range("AH805").Activate
    ActiveCell.FormulaR1C1 = "=AG805/10000"

    Sheets.Select
    Range("AH808").Activate
    ActiveCell.FormulaR1C1 = "=AD806-AD807-AH805"

End Sub

Please any help is appreciated! hank you!

You're using A1-style cell references, so use .Formula not .FormulaR1C1 — Tim Williams
– Tim Williams, Commented Oct 29, 2024 at 22:05
Range("AD805").Formula = "=SUBTOTAL(9,(AD2:AD803))" only needs one line- skip the Activate — Tim Williams
– Tim Williams, Commented Oct 29, 2024 at 22:07
In this way, you can only enter the formula to one worksheet at a time. For many you need a loop. With ActiveCell you can fill all sheets immediately without loops. Only multiple sheet selection is not needed. — MGonet
– MGonet, Commented Oct 29, 2024 at 23:05

rotabor · Accepted Answer · 2024-10-29 23:17:27Z

3

You need to use the Formula property instead of FormulaR1C1 since you use A1-style:

.Formula2 = "=SUBTOTAL(9,(AD2:AD803))"

The next epression is wrong because "-" is not supported operation for strings:

"=SUMIF(AE2:AE803," - ",AD2:AD803)/100"

"need to sum separately depending if there is a 'negative' sign or a 'positive' sign on column AD":

[AD806].Formula2 = "=SUM((AD2:AD803=""+"")*AE3:AE803)/100"
[AD807].Formula2 = "=SUM((AD2:AD803=""-"")*AE3:AE803)/100"

Credits to @MGonet: look at his comment below for another interpretation of requirements and corresponding formulas.

IMPORTANT Use Formula2 property.

Credits to @MGonet: If you have similar sheets and need to enter similar formulas, do the following:

Sheets(Array("Sheet1", "Sheet2")).Select
[AD806].Activate
ActiveCell.Formula2 = "=SUM((AD2:AD803=""+"")*AE3:AE803)/100"
[AD807].Activate
ActiveCell.Formula2 = "=SUM((AD2:AD803=""-"")*AE3:AE803)/100"

Explanation/instruction:

Select all similar sheets including them in Array.
Activate the required cell.
Set formula to the active cell.

As result of the code above, AD806 on all selected sheets will get the same formula, AD807 as well.

edited Oct 29, 2024 at 23:17

answered Oct 29, 2024 at 22:04

rotabor

5,6112 gold badges5 silver badges23 bronze badges

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

6 Comments

Jackie Over a year ago

thank you for this information! i need to sum separately depending if there is a 'negative' sign or a 'positive' sign on column AD. how can i correct the second formula to sum the negative amounts?

MGonet Over a year ago

I understood it differently. But want to sum an AD or AE column? If AD then "=SUMIF(AD2:AD803,"">0"")/100" for positive values and "=SUMIF(AD2:AD803,""<0"")/100" for negative values. If sum of AD in dependence of a sign in AE then "=SUMIF(AE2:AE803,"">0"", AD2:AD803)/100" or <0 accordingly.

rotabor Over a year ago

@MGonet There are two columns mentioned in "=SUMIF(AE2:AE803," + ",AD2:AD803)/100". Since OP writes "'negative' sign or a 'positive' sign on column AD" the only column to sum from those of two is AE. Anyway, OP will correct or ask for correction if any. We can suppose what any we can having no data to look at.

rotabor Over a year ago

@MGonet This trick with ActiveCell and multiple selected sheets is new for me. But I did it manually accidently by mistake, ha-ha.

VBasic2008 Over a year ago

Interesting: If you have charts in the workbook, one will assume that instead of Sheets.Select, Worksheets.Select must be used. But even that is not true: although Sheets.Select will select all the sheets (including charts), the operations will still work without an error.

|

Michal · Accepted Answer · 2024-10-29 22:12:38Z

2

You should try to avoid activating and/or selecting cells:

   Sub InstacartOld()
    
        Dim ws As Worksheet
        
        For Each ws In ThisWorkbook.Worksheets
            ws.Range("AD805").Formula = "=SUBTOTAL(9,AD2:AD803)"
            'the rest goes here
        Next ws

    End Sub

answered Oct 29, 2024 at 22:12

Michal

7,6744 gold badges30 silver badges47 bronze badges

1 Comment

MGonet Over a year ago

But in this particular case, using ActiveCell, you can enter formulas into all sheets at once without loop. I didn't check which is faster with a large number of sheets.

Collectives™ on Stack Overflow

How can I apply a series of formulas across an entire workbook/file

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related