Perplexing Macro Issue

Calling all experts!!

I am working on a project that needs to do some heavy data manipulation to approx 500,000 records per month. I have created a number of macros that are manually run by a user (a single user whom I have trained) on each worksheet within a workbook. The essential functions for each of the 5 macros are:

1. Find and Replace macro that searches for a corrects incorrect or common mispellings.
2. Location macro that separates out the two-letter State abbreviation from a column and creates a new column for it. This macro also 'Paste Special - Values' into a new column and then deletes the column with the Trim formula.
3. Split Cell macro that trims out specific data contained in one column and divides it into 3 columns.
4. Paste Values macro that does the 'Paste Special - Values' function to the above newly created columns and deletes the columns with the Trim formulas.
5. New Sheets macro that cleans up unused rows and hidden columns.

Note: The reason for the 'Paste Special - Values' functions are that this data is imported into an Access Database following manipulation in Excel.

Now the problem.....I have created these macros on my machine in my home office and copied them onto the target machine at the business where it is to be used. Both machines are relatively new running XP and Office 2003. The target machine is on a local, hardwired network.

While I had unqualified, initial success on the target machine for one monthly cycle....now the macros run unacceptably slow on the target machine. I am able to run all five macros through 7 worksheets on my machine in about 30 minutes...perfectly acceptable considering the amount of data! While I have not done a complete run-through on the target machine....it appears that it would take many hours for one worksheet.

I have:

• Turned off network connections (and even unplugged the LAN cable) on the target machine - no help
  
• Made sure that I have a self-certified digital certificate on the target machine for myself and the user
  
• Re-created the macros on the target machine by cutting and pasting the code into new macros

The perplexing issue for me is why is works so flawlessly on my machine, but is slower than a glacier on the target machine.

I have seen the "Screen Updating/Application Calculation" sub that I may try....but I am not overly hopeful!

Any thoughts/advice would be greatly appreciated!!

Thanks!!

A few more thoughts:

The macros are stored in a Personal.xls file that resides in the XLSTART folder in documents&settings/[user]/applications/microsoft/Excel

I would be happy to post the macros, but was reluctant to do so as the post may seem overly complex and scare off some good thoughts!

Thanks!

Hi

1) Presumably the machines are memory size compatible, and virtual memory and other "Physical" type issues have similar characteristics.
2) Are there any other macros in the user's personal.xls that may be impacting? Event macros???
3) Check the priority of Excel on both machines. Open up the task manager, select processes, right click on Excel, set priority and see what both machines show.
4) Is the delay in the processing, or in the opening / closing of the source data files?

Maybe a look at the code would offer some suggestions on speed enhancements, so perhaps create a small workbook, with some of the data, and the code and paste it for review.


HTH

rylo

" Page Break " view can play havoc. When Page Break view is on, the print driver re-evaluates the view at the every line of code where print view changes ( such as deleting a row, hiding rows and columns ). Just swith off the Page Break View ( Tools > Options > View > Page Break ticked off ). Page Break View will make print driver re-evaluate the view even if screen update is off.

A V Veerkar

Thanks for the replies and the advice!

I’ll embed some of the macro coding below for your review. I’ll definitely add this screenupdating/calculation code to each of my macros:

Sub (Your Macro Name)

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


(place your code here)


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

I’ll have to check the hardware properties of the target machine next time I’m out there. I know that there are no other macros/events running in Excel, though checking the priority is an excellent idea….thanks. The delay does occur during processing. I’m pretty sure that page break view is off, but I’ll check that too.

Find/Replace Macro (I’ve pasted only one piece of the code as there are hundreds of similar entries):

Sub FindReplace()
'
' FindReplace Macro
' Macro recorded 3/13/2007 by Me
'
' Keyboard Shortcut: Ctrl+f
'
    Columns("A:A").Select
    Selection.Replace What:="Apple", Replacement:="Red Delicious Apple”, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="Orange", Replacement:="Tangerine”, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
    Selection.Replace What:="Red Grpaes", Replacement:="Red Grapes”, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Location Macro:

Sub SeparateState()
'
' SeparateState Macro
' Macro recorded 8/24/2007 by Me
'
' Keyboard Shortcut: Ctrl+l
'
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[1],2)"
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I65536")
    Range("I2:I65536").Select
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight
    Columns("J:J").Select
    Selection.Copy
    Columns("I:I").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("I1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "State"
    With ActiveCell.Characters(Start:=1, Length:=5).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Columns("I:I").Select
    Columns("I:I").EntireColumn.AutoFit
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
End Sub

Split Cells Macro:

Sub SplitCells()
'
' SplitCells Macro
' Macro recorded 6/4/2007 by Me
'
' Keyboard Shortcut: Ctrl+s
'
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=TRIM(MID(RC[-1],SEARCH("" "",RC[-1])+1,255))"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=TRIM(MID(RC[-1],SEARCH("" "",RC[-1])+1,255))"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=TRIM(MID(RC[-1],SEARCH("" "",RC[-1])+1,255))"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-4],(SEARCH("" "",RC[-4])-1))"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-4],(SEARCH("" "",RC[-4])-1))"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(SEARCH("" "",RC[-4])-1),RC[-4],LEFT(RC[-4],SEARCH("" "",RC[-4])-1))"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(TRIM(MID(RC[-5],SEARCH("" "",RC[-5])+1,255))),"" "",TRIM(MID(RC[-5],SEARCH("" "",RC[-5])+1,255)))"
    Range("B2:H2").Select
    Range("H2").Activate
    ActiveWindow.SmallScroll ToRight:=3
    Selection.AutoFill Destination:=Range("B2:H65536")
    Range("B2:H65536").Select
    Columns("A:D").Select
    Range("D1").Activate
    Selection.EntireColumn.Hidden = True
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Year"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Make"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Model"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Model-other"
    Range("I1").Select
End Sub

Paste Values Macro:

Sub PasteValues()
'
' PasteValues Macro
' Macro recorded 4/27/2007 by Me
'
' Keyboard Shortcut: Ctrl+p
'
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Columns("E:E").Select
    Selection.Copy
    Columns("I:I").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:F").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("J:J").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll ToRight:=-1
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("K:K").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("H:H").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("L:L").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll ToRight:=-5
    Columns("E:H").Select
    Range("H1").Activate
    Selection.EntireColumn.Hidden = True
End Sub

New Sheets Macro:

Sub NewSheets()
'
' NewSheets Macro
' Macro recorded 7/10/2007 by Me
'
' Keyboard Shortcut: Ctrl+n
'
    Range("I1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets.Add
    ActiveSheet.Paste
    Range("A1").Select
 End Sub

Whew! If you’ve read through all that….Thanks!!!! Any thoughts would be appreciated.

Digger442,

All the suggestions that are coming will certainly improve the performance of the macro, no about it. But the primary issue is why so much of difference between the two machines which are basically of same config. Do share the when you eventually find out.

A V Veerkar

Thanks for the feedback.

Rylo - I'll have to examine your suggestions a bit later and will let you know....suggestions look great at first look though!

A V - Bingo! That is the most puzzling thing for me too. I'll certainly let you know after I get a chance to definitively compare machines, check priorities, review processes, etc

Šigger

Hi

Bit more. The Paste Values macro can be reduced to

Columns("I:L").Insert shift:=xlToRight
  Range("I:L").Value = Range("E:H").Value
  Columns("E:H").EntireColumn.Hidden = True
  Range("H1").Select

rylo

Hi

Sent off an email. Would pay to remove the address from the post - spam magnet otherwise.


rylo