Problem with macro speed when placed on network drive

madchemist

Board Regular
Joined
Jul 10, 2006
Messages
198
The following macro takes less than 2 seconds to execute when stored on my local machine. Once I load it onto the network drive and open it, it takes over 30 seconds. It even takes forever if I then take it off the network drive and open it locally on my machine. Even the original file that wasn't loaded onto the network takes forever.

Does anyone have any idea why it would do this? I really need to get around this problem. This thing works so beautifully minus this issue. Thanks!

Code:
Application.ScreenUpdating = False
    Sheets(PIA).Select
    [H2].Select
    Selection.NumberFormat = "@"
    frmAdd.Show
    [B2].FormulaR1C1 = PIA
    Sheets("Action Item Trends").Select

        
    [C9].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C10].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C11].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],2.06)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
    [C12].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C13].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],3.01)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
    [C14].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],3.02)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
    [C15].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C16].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C17].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],5.02)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
    [C18].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],5.05)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
    [C19].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C20].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C21].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],7.08)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
    [C22].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C23].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C24].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],9.01)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
    [C25].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C26].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],10.01)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
    [C43].FormulaR1C1 = "=SUMPRODUCT(--('" & PIA & "'!R2C16:R2500C16=""Open""),'" & PIA & "'!R2C17:R2500C17)/COUNTIF('" & PIA & "'!R2C16:R2500C16,""Open"")"
    [C44].FormulaR1C1 = "=SUMPRODUCT(--('" & PIA & "'!R2C16:R2500C16=""Closed""),'" & PIA & "'!R2C17:R2500C17)/COUNTIF('" & PIA & "'!R2C16:R2500C16,""Closed"")"
    [C45].FormulaR1C1 = "=COUNTIF('" & PIA & "'!R2C16:R2500C16,""Closed"")"

    Sheets(PIA).Select
    [C2].Select

Code from frmadd

Code:
Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    Selection.Rows.AutoFit

    [H2].Select
    Selection.NumberFormat = "@"
    
    Range("A2:D2,F2:J2,L2:P2,R2:S2").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    
    [C2].Select
    ActiveCell.Value = txtSubject.Value
    ActiveCell.Offset(0, 1) = txtDescription.Value
    ActiveCell.Offset(0, 3) = txtDCF.Value
    ActiveCell.Offset(0, 9) = Application.UserName
    ActiveCell.Offset(0, 4) = ComboBox_Responsible.Value
    ActiveCell.Offset(0, 5) = txtCode.Value
    ActiveCell.Offset(0, 6) = ComboBox_Severity.Value
    ActiveCell.Offset(0, 7) = ComboBox_Frequency.Value
    ActiveCell.Offset(0, 10) = txtDue.Value
    ActiveCell.Offset(0, 16) = txtComments.Value
    
    Range("A2:S2").Select
    Selection.Font.Bold = False
    Selection.Interior.ColorIndex = xlNone
    With Selection.Font
        .Name = "Arial"
        .Size = 8
    End With
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = True
    End With
    [A2:C2,E2:R2].Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
    End With
    [D2,S2].Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = True
    End With

    [A2:M2:N2:R2].Select
    Selection.NumberFormat = "[$-409]d-mmm-yy;@"
    [B2:L2,O2:Q2,S2].Select
    Selection.NumberFormat = "General"
    [H2].Select
    Selection.NumberFormat = "@"
    [K2].FormulaR1C1 = "=INDEX({2,3,5},1,MATCH(RC[-2],{""Low"",""Mod"",""High""},0))*INDEX({1,2,3},1,MATCH(RC[-1],{""Low"",""Mod"",""High""},0))"
    [P2].FormulaR1C1 = "Open"
    [P2].Select
    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
    [Q2].FormulaR1C1 = "=NETWORKDAYS(RC[-16],IF(RC[-1]=""open"",TODAY(),IF(RC[-1]=""escalated"",RC[1],RC[-3])))"
    
    [A2].FormulaR1C1 = "=TODAY()"
    [A2].Select
    Selection.Copy
    [A2].Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    [K2].Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="9"
    Selection.FormatConditions(1).Interior.ColorIndex = 7
    [H2].Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=ISBLANK(H2)"
    Selection.FormatConditions(1).Interior.ColorIndex = 3
    
    Casegenerater
    Unload Me

Code from Casegenerater

Code:
Sub Casegenerater()
    Prefix = Strings.Left(ActiveSheet.Name, 4)
    Count = 0
    For i = 1 To Cells(Rows.Count, "E").End(xlUp).Row
        If Strings.Left(Cells(i, "E"), 4) = Prefix Then Count = Count + 1
    Next i
    CaseCount = Count + 1
    Cells(2, "E").Value = Prefix & "-" & Format(CaseCount, "0000")
End Sub
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
You'll have better results if you tweak your code to eliminate all of the select statements.

Here's an example:

Code:
With Rows("2:2")
    .Insert Shift:=xlDown
    .Rows.AutoFit
End With

    [H2].NumberFormat = "@"
    
    With Range("A2:D2,F2:J2,L2:P2,R2:S2")
        .Locked = False
        .FormulaHidden = False
    End With
    
    With Range("C2")
        .Value = txtSubject.Value
        .Offset(0, 1) = txtDescription.Value
        .Offset(0, 3) = txtDCF.Value
        .Offset(0, 9) = Application.UserName
        .Offset(0, 4) = ComboBox_Responsible.Value
        .Offset(0, 5) = txtCode.Value
        .Offset(0, 6) = ComboBox_Severity.Value
        .Offset(0, 7) = ComboBox_Frequency.Value
        .Offset(0, 10) = txtDue.Value
        .Offset(0, 16) = txtComments.Value
    End With
    
    With Range("A2:S2")
        .Font.Bold = False
        .Interior.ColorIndex = xlNone
            With .Font
                .Name = "Arial"
                .Size = 8
            End With
        .VerticalAlignment = xlBottom
        .WrapText = True
    End With

As for the speed issue you can try to set caculation to manual until everything's done and calculate at the end of the code.

HTH,

Smitty
 

madchemist

Board Regular
Joined
Jul 10, 2006
Messages
198
wow, the manual calculation made it instantaneous. So could I change it from manual to automatic at the beginning of the code and then change it back and have it calculate at the end of the code? how exactly would I do this? Is this going to take a ton of time to calculate everything then?


Thanks Smitty!
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
wow, the manual calculation made it instantaneous. So could I change it from manual to automatic at the beginning of the code and then change it back and have it calculate at the end of the code? how exactly would I do this? Is this going to take a ton of time to calculate everything then?

Just use Application.Calculation = xlManual and set it back to automatic at the end.

Smitty
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
You might also want to stick a Calculate in there too.
 

madchemist

Board Regular
Joined
Jul 10, 2006
Messages
198
You might also want to stick a Calculate in there too.

So just put the command calculate somewhere within the code is what you are saying?


Thanks Smitty and Norie. The code is working much better now. I have gone through and cleaned it all up and done the switch to manual and then back to automatic.

Where would you put the calculate command? Thanks!
 

Forum statistics

Threads
1,181,098
Messages
5,928,055
Members
436,586
Latest member
latintxn

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
Top