VBA to insert formula in multiple sheets

A

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,434
hello Guys,

I have following code where I am trying to insert array formulas n same ranges in different sheets.
Code: 
Sub RunSheets()Dim FormulaOneA As String, FormulaOneB As String, FormulaTwoA As String, FormulaTwoB As String, FormulaTwoC As String, FormulaThreeA As String, FormulaThreeB As String, FormulaThreeC As String
Dim FormulaFourA As String, FormulaFourB As String
Dim ws As Worksheet
Dim ar As Areas


    For Each ws In Worksheets(Array("Sun Run Sheet", "Mon Run Sheet", "Tue Run Sheet", "Wed Run Sheet", "Thu Run Sheet", "Fri Run Sheet", "Sat Run Sheet"))
        With ws.Range("A5:O170")
    
            ws.Range("A5").FormulaArray = "=IFERROR(INDEX('Timetarget Roster Export'!$S$1:$S$3000,MATCH(1,('Timetarget Roster Export'!$D$1:$D$3000=$B$3)*('Timetarget Roster Export'!$B$1:$B$3000=B5),0)),"""")"
            ws.Range("B5").FormulaArray = "=IFERROR(INDEX('Timetarget Roster Export'!$B$1:$B$3000,SMALL(IF(('Timetarget Roster Export'!$D$2:$D$3000=$B$3)*(('Timetarget Roster Export'!$E$2:$E$3000)*1=F5),ROW('Timetarget Roster Export'!$E$2:$E$3000)),COUNTIF(F$5:F5,F5))),"""")"
            ws.Range("C5").FormulaArray = "=IF(D5=""Vacant"","""",IF(D5="""","""",IFERROR(VLOOKUP(D5*1,Emp!$B$2:$E$350,4,FALSE),"""")))"


[COLOR=#ff0000]                ws.Range("A5").Copy ws.Range("A6:A170")[/COLOR]
                ws.Range("B5").Copy ws.Range("B6:B170")
                ws.Range("C5").Copy ws.Range("C6:C170")
                


        End With
    Next ws


End Sub

The code worked when I entered the formula in one cell in each sheet. But it gave ma an error and the line in red above gets highlighted in yellow when I added the next bit trying to copy one cell in the range below for all sheets.
What is it that I am doing wrong?
Regards

Asad
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
I have tried this as well but without success
Code: 
Sub RunSheets()
Dim ws As Worksheet

    For Each ws In Worksheets(Array("Sun Run Sheet", "Mon Run Sheet", "Tue Run Sheet", "Wed Run Sheet", "Thu Run Sheet", "Fri Run Sheet", "Sat Run Sheet"))
        With ws.Range("A5:O170")
            Do
                Range("A5").FormulaArray = "=IFERROR(INDEX('Timetarget Roster Export'!$S$1:$S$3000,MATCH(1,('Timetarget Roster Export'!$D$1:$D$3000=$B$3)*('Timetarget Roster Export'!$B$1:$B$3000=B5),0)),"""")"
                Range("B5").FormulaArray = "=IFERROR(INDEX('Timetarget Roster Export'!$B$1:$B$3000,SMALL(IF(('Timetarget Roster Export'!$D$2:$D$3000=$B$3)*(('Timetarget Roster Export'!$E$2:$E$3000)*1=F5),ROW('Timetarget Roster Export'!$E$2:$E$3000)),COUNTIF(F$5:F5,F5))),"""")"
                Range("C5").FormulaArray = "=IF(D5=""Vacant"","""",IF(D5="""","""",IFERROR(VLOOKUP(D5*1,Emp!$B$2:$E$350,4,FALSE),"""")))"


                Range("A5").Copy Range("A6:A170")
                Range("B5").Copy Range("B6:B170")
                Range("C5").Copy Range("C6:C170")
            Loop


        End With
    Next ws


End Sub
 
Last edited:
Upvote 0
Just to jumpstart the possible reasons, two issues pop out at me.

One is this line
With ws.Range("A5:O170")
which is not needed; only the With ws is needed. Then, you can delete the preceding ws references in the next 6 lines of code.

The other issue is that this
.Range("A5").FormulaArray = ...
can be
.Range("A5:A170"),FormulaArray = ...
and so on for the other 2 formulas.
Notice the precedent ws is omitted here because it's in the With structure for that reference per my mention above.
Then of course, delete the lines
ws.Range("A5").Copy ws.Range("A6:A170")
ws.Range("B5").Copy ws.Range("B6:B170")
ws.Range("C5").Copy ws.Range("C6:C170")


Anyway, see if this helps or if I missed the point of your objective.
 
Last edited:
Upvote 0
Thanks a lot Tom. But it causes another issue.
That solved a part of the problem. But if I use this method
Code: 
[COLOR=#333333].Range("A5:A170"),FormulaArray = ...[/COLOR]
instead of entering the formula in one cell and copying it to the range, then the relative cell does not change in rest of the range. For example in formula
Code: 
[COLOR=#333333]"=IF(D5=""Vacant"","""",IF(D5="""","""",IFERROR(VLOOKUP(D5*1,Emp!$B$2:$E$350,4,FALSE),"""")))"[/COLOR]
, "D5" is a rlative refrence and it should go to D6, D7 and so on. But when i use your method, it stays as D5 for all the cells which gives me same answer for all cells.
 
Upvote 0
I have also tried this method which was given by another guru on MrExcel on one of my other questions here ;https://www.mrexcel.com/forum/excel-questions/1015806-va-help-enter-array-formula.html
Code: 
Sub RunSheets()Dim ws As Worksheet
    For Each ws In Worksheets(Array("Sun Run Sheet", "Mon Run Sheet", "Tue Run Sheet", "Wed Run Sheet", "Thu Run Sheet", "Fri Run Sheet", "Sat Run Sheet"))
        With ws
            
                .Range("A5").FormulaArray = "=IFERROR(INDEX('Timetarget Roster Export'!$S$1:$S$3000,MATCH(1,('Timetarget Roster Export'!$D$1:$D$3000=$B$3)*('Timetarget Roster Export'!$B$1:$B$3000=B5),0)),"""")"
                .Range("B5").FormulaArray = "=IFERROR(INDEX('Timetarget Roster Export'!$B$1:$B$3000,SMALL(IF(('Timetarget Roster Export'!$D$2:$D$3000=$B$3)*(('Timetarget Roster Export'!$E$2:$E$3000)*1=F5),ROW('Timetarget Roster Export'!$E$2:$E$3000)),COUNTIF(F$5:F5,F5))),"""")"
                .Range("C5").FormulaArray = "=IF(D5=""Vacant"","""",IF(D5="""","""",IFERROR(VLOOKUP(D5*1,Emp!$B$2:$E$350,4,FALSE),"""")))"


                .Range("A5:A170").FillDown
                .Range("B5:B170").FillDown
                .Range("C5:C170").FillDown
 
        End With
    Next ws


End Sub
but as you can see that was on one sheet. Now I am trying to do the same for multiple sheets.
 
Last edited:
Upvote 0
I guess I will have to do something like this for each individual sheet. This does work, but I would have preferred if I could loop through the sheets.
Code: 
Sub RunSheets1()
Sheets("Sun Run Sheet").Activate
With ThisWorkSheet
   
          
                Range("A5").FormulaArray = "=IFERROR(INDEX('Timetarget Roster Export'!$S$1:$S$3000,MATCH(1,('Timetarget Roster Export'!$D$1:$D$3000=$B$3)*('Timetarget Roster Export'!$B$1:$B$3000=B5),0)),"""")"
                Range("A5:A170").FillDown
End With
Sheets("Mon Run Sheet").Activate
With ThisWorkSheet
   
          
                Range("A5").FormulaArray = "=IFERROR(INDEX('Timetarget Roster Export'!$S$1:$S$3000,MATCH(1,('Timetarget Roster Export'!$D$1:$D$3000=$B$3)*('Timetarget Roster Export'!$B$1:$B$3000=B5),0)),"""")"
                Range("A5:A170").FillDown
End With






End Sub
 
Last edited:
Upvote 0
I finally got this one working, but I am pretty sure that you guys can give me a cleaner and better solution. I have this at the moment
Code: 
Sub RunSheets()Dim ws As Worksheet


    For Each ws In Worksheets(Array("Sun Run Sheet", "Mon Run Sheet", "Tue Run Sheet", "Wed Run Sheet", "Thu Run Sheet", "Fri Run Sheet", "Sat Run Sheet"))
        With ws
           
                .Range("A5").FormulaArray = "=IFERROR(INDEX('Timetarget Roster Export'!$S$1:$S$3000,MATCH(1,('Timetarget Roster Export'!$D$1:$D$3000=$B$3)*('Timetarget Roster Export'!$B$1:$B$3000=B5),0)),"""")"
                .Range("B5").FormulaArray = "=IFERROR(INDEX('Timetarget Roster Export'!$B$1:$B$3000,SMALL(IF(('Timetarget Roster Export'!$D$2:$D$3000=$B$3)*(('Timetarget Roster Export'!$E$2:$E$3000)*1=F5),ROW('Timetarget Roster Export'!$E$2:$E$3000)),COUNTIF(F$5:F5,F5))),"""")"
                .Range("C5").FormulaArray = "=IF(D5=""Vacant"","""",IF(D5="""","""",IFERROR(VLOOKUP(D5*1,Emp!$B$2:$E$350,4,FALSE),"""")))"
            
        End With
        With ws
        .Range("A5:A170").FillDown
        .Range("B5:B170").FillDown
        .Range("C5:C170").FillDown
        End With
    Next ws


End Sub
 
Upvote 0
The code in post 5, spite I've not tested it, seems perfect for me.
I don't understand why you are you saying it runs on just one sheet - I can see an array of sheet names.
Have you tested it?

M.
 
Upvote 0
Now I am embarrassed.
It did not work before, or at least I thought that it was not working before.
But now I tested again, and it is working.
Thanks Marcelo.
 
Last edited:
Upvote 0
asad said:
Now I am embarrassed.
It did not work before, or at least I thought that it was not working before.
But now I tested again, and it is working.
Thanks Marcelo.
Click to expand...

You are welcome.
Maybe you can replace the three fill down by (not tested)
.Range("A5:C170").FillDown

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Automatically move cells from one sheet to another based on criteria using VBA
Replies
5
Views
761
DanteAmor
DanteAmor
R
  • Question Question
Runtime Error 91
2
Replies
15
Views
637
rtr1811
R
B
  • Question Question
VBA exports worksheet from XLSM to XLXS matching all source properties doesn't keep print area
Replies
0
Views
100
brentcole
B
M
  • Question Question
VBA Subtotal 47,000+ Rows
Replies
1
Views
226
igold
igold
V
  • Question Question
Excel VBA - How to modify my code to include a command that prints data to PDF
Replies
1
Views
523
VanGirl
V

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top