Make this code run quicker to overcome its lag

[ipbr21054]

I have the following code but a bit long winded.
Can you please advise how it can be simplified to run smoother / lag free.
Thanks

Private Sub PasteIfFormulas_Click()
     Dim WS As Worksheet

     Set WS = Worksheets("INV")

     WS.Range("G14").Formula = "=IFERROR(IF(INDEX(DATABASE!R:R,$H$13)=0,"""",INDEX(DATABASE!R:R,$H$13)),"""")"
     WS.Range("G14").Formula = "=IFERROR(IF(INDEX(DATABASE!R:R,$H$13)=0,"""",INDEX(DATABASE!R:R,$H$13)),"""")"
     WS.Range("G15").Formula = "=IFERROR(IF(INDEX(DATABASE!S:S,$H$13)=0,"""",INDEX(DATABASE!S:S,$H$13)),"""")"
     WS.Range("G16").Formula = "=IFERROR(IF(INDEX(DATABASE!T:T,$H$13)=0,"""",INDEX(DATABASE!T:T,$H$13)),"""")"
     WS.Range("G17").Formula = "=IFERROR(IF(INDEX(DATABASE!U:U,$H$13)=0,"""",INDEX(DATABASE!U:U,$H$13)),"""")"
     WS.Range("G18").Formula = "=IFERROR(IF(INDEX(DATABASE!V:V,$H$13)=0,"""",INDEX(DATABASE!V:V,$H$13)),"""")"
     
     WS.Range("L14").Formula = "=IFERROR(IF(INDEX(DATABASE!D:D,$H$13)=0,"""",INDEX(DATABASE!D:D,$H$13)),"""")"
     WS.Range("L15").Formula = "=IFERROR(IF(INDEX(DATABASE!B:B,$H$13)=0,"""",INDEX(DATABASE!B:B,$H$13)),"""")"
     WS.Range("L16").Formula = "=IFERROR(IF(INDEX(DATABASE!L:L,$H$13)=0,"""",INDEX(DATABASE!L:L,$H$13)),"""")"
     WS.Range("L17").Formula = "=IFERROR(IF(INDEX(DATABASE!W:W,$H$13)=0,"""",INDEX(DATABASE!W:W,$H$13)),"""")"
     
     WS.Range("M27").Formula = "=IF(AND(H27="""",H27=""""),"""",H27*J27)"
     WS.Range("M28").Formula = "=IF(AND(H28="""",H28=""""),"""",H28*J28)"
     WS.Range("M29").Formula = "=IF(AND(H29="""",H29=""""),"""",H29*J29)"
     WS.Range("M30").Formula = "=IF(AND(H30="""",H30=""""),"""",H30*J30)"
     WS.Range("M31").Formula = "=IF(AND(H31="""",H31=""""),"""",H31*J31)"
     WS.Range("M32").Formula = "=IF(AND(H32="""",H32=""""),"""",H32*J32)"
     WS.Range("M33").Formula = "=IF(AND(H33="""",H33=""""),"""",H33*J33)"
     WS.Range("M34").Formula = "=IF(AND(H34="""",H34=""""),"""",H34*J34)"
     WS.Range("M35").Formula = "=IF(AND(H35="""",H35=""""),"""",H35*J35)"
     WS.Range("M36").Formula = "=IF(AND(H36="""",H36=""""),"""",H36*J36)"
     
End Sub

 

[Joe4]

See if this helps:

Private Sub PasteIfFormulas_Click()
     Dim WS As Worksheet

     Application.ScreenUpdating = False
     Application.Calculation = xlCalculationManual

     Set WS = Worksheets("INV")

     WS.Range("G14").Formula = "=IFERROR(IF(INDEX(DATABASE!R:R,$H$13)=0,"""",INDEX(DATABASE!R:R,$H$13)),"""")"
     WS.Range("G15").Formula = "=IFERROR(IF(INDEX(DATABASE!S:S,$H$13)=0,"""",INDEX(DATABASE!S:S,$H$13)),"""")"
     WS.Range("G16").Formula = "=IFERROR(IF(INDEX(DATABASE!T:T,$H$13)=0,"""",INDEX(DATABASE!T:T,$H$13)),"""")"
     WS.Range("G17").Formula = "=IFERROR(IF(INDEX(DATABASE!U:U,$H$13)=0,"""",INDEX(DATABASE!U:U,$H$13)),"""")"
     WS.Range("G18").Formula = "=IFERROR(IF(INDEX(DATABASE!V:V,$H$13)=0,"""",INDEX(DATABASE!V:V,$H$13)),"""")"
     
     WS.Range("L14").Formula = "=IFERROR(IF(INDEX(DATABASE!D:D,$H$13)=0,"""",INDEX(DATABASE!D:D,$H$13)),"""")"
     WS.Range("L15").Formula = "=IFERROR(IF(INDEX(DATABASE!B:B,$H$13)=0,"""",INDEX(DATABASE!B:B,$H$13)),"""")"
     WS.Range("L16").Formula = "=IFERROR(IF(INDEX(DATABASE!L:L,$H$13)=0,"""",INDEX(DATABASE!L:L,$H$13)),"""")"
     WS.Range("L17").Formula = "=IFERROR(IF(INDEX(DATABASE!W:W,$H$13)=0,"""",INDEX(DATABASE!W:W,$H$13)),"""")"
     
     WS.Range("M27:M36").Formula = "=IF(AND(H27="""",H27=""""),"""
     
     Application.Calculation = xlCalculationAutomatic
     Application.ScreenUpdating = True
     
End Sub

 

[ipbr21054]

Hi,
I get a RTE 1004
application defind error.

This line in yellow

WS.Range("M27:M36").Formula = "=IF(AND(H27="""",H27=""""),"""

 

[ipbr21054]

With that line removed i then run the code & no RTe shown.
So something that line doesnt like

 

[Joe4]

You can change that part abck to what you had originally:

Private Sub PasteIfFormulas_Click()
     Dim WS As Worksheet

     Application.ScreenUpdating = False
     Application.Calculation = xlCalculationManual

     Set WS = Worksheets("INV")

     WS.Range("G14").Formula = "=IFERROR(IF(INDEX(DATABASE!R:R,$H$13)=0,"""",INDEX(DATABASE!R:R,$H$13)),"""")"
     WS.Range("G15").Formula = "=IFERROR(IF(INDEX(DATABASE!S:S,$H$13)=0,"""",INDEX(DATABASE!S:S,$H$13)),"""")"
     WS.Range("G16").Formula = "=IFERROR(IF(INDEX(DATABASE!T:T,$H$13)=0,"""",INDEX(DATABASE!T:T,$H$13)),"""")"
     WS.Range("G17").Formula = "=IFERROR(IF(INDEX(DATABASE!U:U,$H$13)=0,"""",INDEX(DATABASE!U:U,$H$13)),"""")"
     WS.Range("G18").Formula = "=IFERROR(IF(INDEX(DATABASE!V:V,$H$13)=0,"""",INDEX(DATABASE!V:V,$H$13)),"""")"
     
     WS.Range("L14").Formula = "=IFERROR(IF(INDEX(DATABASE!D:D,$H$13)=0,"""",INDEX(DATABASE!D:D,$H$13)),"""")"
     WS.Range("L15").Formula = "=IFERROR(IF(INDEX(DATABASE!B:B,$H$13)=0,"""",INDEX(DATABASE!B:B,$H$13)),"""")"
     WS.Range("L16").Formula = "=IFERROR(IF(INDEX(DATABASE!L:L,$H$13)=0,"""",INDEX(DATABASE!L:L,$H$13)),"""")"
     WS.Range("L17").Formula = "=IFERROR(IF(INDEX(DATABASE!W:W,$H$13)=0,"""",INDEX(DATABASE!W:W,$H$13)),"""")"
     
     WS.Range("M27").Formula = "=IF(AND(H27="""",H27=""""),"""",H27*J27)"
     WS.Range("M28").Formula = "=IF(AND(H28="""",H28=""""),"""",H28*J28)"
     WS.Range("M29").Formula = "=IF(AND(H29="""",H29=""""),"""",H29*J29)"
     WS.Range("M30").Formula = "=IF(AND(H30="""",H30=""""),"""",H30*J30)"
     WS.Range("M31").Formula = "=IF(AND(H31="""",H31=""""),"""",H31*J31)"
     WS.Range("M32").Formula = "=IF(AND(H32="""",H32=""""),"""",H32*J32)"
     WS.Range("M33").Formula = "=IF(AND(H33="""",H33=""""),"""",H33*J33)"
     WS.Range("M34").Formula = "=IF(AND(H34="""",H34=""""),"""",H34*J34)"
     WS.Range("M35").Formula = "=IF(AND(H35="""",H35=""""),"""",H35*J35)"
     WS.Range("M36").Formula = "=IF(AND(H36="""",H36=""""),"""",H36*J36)"
     
     Application.Calculation = xlCalculationAutomatic
     Application.ScreenUpdating = True
     
End Sub

The important parts are the lines I added highlighted in red.
They should speed up the code.

 

[ipbr21054]

Thanks now done.

Seems quicker by the way

 

[Joe4]

Thanks now done.

You are welcome.

Seems quicker by the way

That was the whole point of the question, right?
Suppressing calculations and screen updating until the end of the code often helps speed it up, especially if you are doing a lot of cell/formula updates.

 

[ipbr21054]

Hi,
Yes it was but just advising the outcome to confirm it

Thanks

 

[Joe4]

Hi,
Yes it was but just advising the outcome to confirm it

Thanks

No problem.
Your wording on that reply was just a little strange.
"By the way" seems to imply that it was an unexpected, secondary outcome, when that was the goal of my code all along (as I thought that is precisely what you were asking for).