Large file applying formulas via VBA help

[ItalianPlatinum]

I have a large file, and i moved the formulas into VBA looking to help the speed but it isn't much improvement. I was able to narrow down the formula to one formula that is taking the most time. Does anyone have any suggestions to help alleviate this? i am up for anything. the file has 400k rows and 18 columns. columns S-AA i set the formulas.

    .Range("W5:W" & lr).Formula = "=IF(AND(U5=TRUE,V5=TRUE),I5,WORKDAY(I5,1,HOLIDAYS!A$1:A$65536))"

'formatting for recon sheet'formatting and formulas for recon sheet
With WsCalc
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    .Range("S5:S" & lr).Formula = "=IF(L5>19000000,""Y"",""N"")"
    .Range("T5:T" & lr).Formula = "=H5-G5"
    .Range("U5:U" & lr).Formula = "=I5=H5"
    .Range("V5:V" & lr).Formula = "=I5=J5"
    .Range("W5:W" & lr).Formula = "=IF(AND(U5=TRUE,V5=TRUE),I5,WORKDAY(I5,1,HOLIDAYS!A$1:A$65536))"
    .Range("X5:X" & lr).Formula = "=IF(WEEKDAY(G5,2)>=5,""Y"",""N"")"
    .Range("Y5:Y" & lr).Formula = "=IF(AND(U5=TRUE,V5=TRUE),""Y"",""N"")"
    .Range("Z5:Z" & lr).Formula = "=IF(I5<>J5,""Y"",""N"")"
    .Range("AA5:AA" & lr).Formula = "=IF(AND(S5=""N"",T5>0,X5=""N""),""Y"",""N"")"
    .Range("S5:AA" & lr).Value = .Range("S5:AA" & lr).Value
    .Columns("A:AA").EntireColumn.AutoFit
    '.Range("2:2").AutoFilter 20, "Yes"
    .Application.Goto Reference:=.Range("A1"), Scroll:=True
    .Activate
End With

also thanks in advance

 

[SanjayGMusafir]

.Range("W5:W" & lr).Formula = "=IF(AND(U5=TRUE,V5=TRUE),I5,WORKDAY(I5,1,HOLIDAYS!A$1:A$65536))"

It's kind of lookup function/feature. The always slow down

Best is to make calculations to manual in settings and see the difference...

 

[ItalianPlatinum]

Yea i was worried about that. So seems like i dont have much options

 

[SanjayGMusafir]

Yea i was worried about that. So seems like i dont have much options

At times it's better to live with the system's limitations than to fight it...

 

[RoryA]

Do you really need to use 65k rows for holidays??

 

[ItalianPlatinum]

I need to assess each date in each row if the date is a business day (working day). I dont know another way to do that.

 

[RoryA]

Do you really have 65k rows of holiday dates? If not, why use HOLIDAYS!A$1:A$65536

 

[RoryA]

Also, since you're doing the same calculation in column Y that you did at the start of the column W formula, you should swap them round and just use the Y result in W

    .Range("Y5:Y" & lr).Formula = "=IF(AND(U5=TRUE,V5=TRUE),""Y"",""N"")"
    .Range("W5:W" & lr).Formula = "=IF(Y5=""Y"",I5,WORKDAY(I5,1,HOLIDAYS!A$1:A$65536))"
    .Range("X5:X" & lr).Formula = "=IF(WEEKDAY(G5,2)>=5,""Y"",""N"")"

 

[ItalianPlatinum]

Do you really have 65k rows of holiday dates? If not, why use HOLIDAYS!A$1:A$65536

oh yea that you are correct..... ill adjust

 

[ItalianPlatinum]

Also, since you're doing the same calculation in column Y that you did at the start of the column W formula, you should swap them round and just use the Y result in W

    .Range("Y5:Y" & lr).Formula = "=IF(AND(U5=TRUE,V5=TRUE),""Y"",""N"")"
    .Range("W5:W" & lr).Formula = "=IF(Y5=""Y"",I5,WORKDAY(I5,1,HOLIDAYS!A$1:A$65536))"
    .Range("X5:X" & lr).Formula = "=IF(WEEKDAY(G5,2)>=5,""Y"",""N"")"

so you mean just combine the formulas? i was afraid that would take longer so i broke it out