excel VBA very slow updating xlpart in formulas

Spikenaylor

Board Regular
Joined
Apr 14, 2013
Messages
116
Office Version
  1. 365
Platform
  1. Windows
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


VBA Code:
   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

VBA Code:
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
you could try to see if this modification speeds it up a bit. It reduces the number of accesses to the worksheets:
VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

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