excel VBA very slow updating xlpart in formulas

[Spikenaylor]

This used to be quite fast, nothing has changed except software updates and office updates. now using business office 365
standard stuff set including
Application.Calculation = xlManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

   For i = 2 To 1801

            Application.StatusBar = "Updating Pivot Sheet data links for row " & i
            Range("EY" & i & ":GI" & i).Replace strCurrentYear, strNewYear, xlPart
    Next i

have tried this as a test

For i = 2 To 1801
 Application.StatusBar = "Updating IPC Pivot Sheet data links for row " & i
 Set acell = ActiveSheet.Range("EY" & i & ":GI" & i).Find(What:=strCurrentYear, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
 If Not acell Is Nothing Then
  acell.Formula = Replace(acell.Formula, strCurrentYear, strNewYear)
 End If
 Next i

This coding only gets run once a year when updating the spreadsheet for a new year.

both work but extremely slowly, like I said the first code worked fine upto some point. No Idea why code like this runs very slowly now?

anyone got any ideas or guidance

thanks for looking

Regards

 

[offthelip]

you could try to see if this modification speeds it up a bit. It reduces the number of accesses to the worksheets:

Sub rpl()
Dim eyi As String
Dim gii As String

            eyarr = Range("Ey1:ey1801").Formula
            giarr = Range("gi1:1801").Formula
              
   For i = 2 To 1801

            eyi = eyarr(i, 1)
            gii = giarr(i, 1)
            If InStr(eyi, strcurrentYear) Or InStr(gii, strcurrentYear) Then
                       Application.StatusBar = "Updating Pivot Sheet data links for row " & i
                       Range("EY" & i & ":GI" & i).Replace strcurrentYear, strNewYear, xlPart
            End If
            
    Next i
End Sub

The other thought I had was wondering whether it was some interaction due to Office 365; have you tried running the code when you are offline and not logged into office 365, presuming you have set up working off line . Note I don't use office 365 so I am guessing

 

[Spikenaylor]

Many thanks
tried the working offline option first and it worked
dont understand why it would make a difference but it does
all ok now
till my next issue

regards

 

[offthelip]

Another reason not to upgrade ( downgrade?) to Office 365!! Every bit of software that I have got that requires me to login to use it, seems to have problems with speed at times, something that never seems to happens with software licensed the traditional way.