Macro that ran in Excel 2010 VBA doesn't work in 2000 VBA.

[billfinn]

Good morning,
I need to make several macros created on Excel 2010 VBA run on Excel 2000 and so far it's been a real clinic. The macros run perfectly on Excel 2010.
I got one working after a while, but this next has me stymied. It's pretty much over my head. I commented out the error handler so I could track down the error.

The macro errors on the line;
Windows("Estimator.xlsm").Activate

I tried replacing Windows with Workbooks but got the same error.

If I comment that line out as a test
it next complains about the line;
Range("A:D").Replace Ary(i), "=xxx", xlWhole, , False, , False, FalseI will be extremely grateful for any suggestion or input
Thanks much!
Bill

Sub Strip_Page_Report_Headers()    'Macro to strip out Report and Page headings to leave an Excel File with columns properly formatted.
'Timer - comment out the three lines below if you don't want the timer to run
Dim StartTime As Double
Dim MinutesElapsed As String
'Remember time when macro starts
StartTime = Timer


    On Error GoTo ErrorHandler
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .PrintCommunication = False
End With


    Windows("Estimator.xlsm").Activate  'Go to the Estimating Tool
    Sheets("Item Master").Select      'Select the Test Data worksheet in the Estimating tool
    Selection.EntireColumn.Hidden = False
    Dim Ary As Variant
    Dim i As Long
    Ary = Array("*QUERY*", "*INVMASTB*", "*INVMASTAAA*", "*LIBRARY*", "*PRICEMSM*", "*DATE*", "*REPORT", "*invmast*", "Item", "Prod", "Pricing", "*Cost*", "*:*", "*DELETED*", "*EDIORGI*", "*DELETE*", "*FORMAT*")
    For i = 0 To UBound(Ary)
    Range("A:D").Replace Ary(i), "=xxx", xlWhole, , False, , False, False
    Next i


    For i = 1 To 15
    On Error Resume Next
    Columns(i).SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
    On Error GoTo 0
Next


'Add Column Labels
Rows(1).Insert Shift:=xlDown
    Range("A1").FormulaR1C1 = "ITEM"
    Range("B1").FormulaR1C1 = "DESCRIPTION"
    Range("C1").FormulaR1C1 = "COST"


  ' Set Column C as currency
Worksheets("Item Master").Columns("C").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"


Columns.AutoFit
  
ErrorHandler:
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .PrintCommunication = True
End With


'Timer end code - comment out two lines below if you don't want the timer to run
  MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
  MsgBox "This code ran successfully in " & MinutesElapsed, vbInformation




End Sub

 

[billfinn]

Got the first problem resolved. I had to put the path in the Activate statement. Now trying to figure out the Range problem.
Bill

 

[JLGWhiz]

It is probably the "=xxx" that is throwing the error. You are replacing a string with a formula that appears to be a bad reference. At least I can't figure out what the 'xxx' is supposed to return.

 

[billfinn]

JLGwhiz,
Thanks much for the response. Someone else helped me to put this one together and I don't know much about that verbiage. I believe it is describing what should be left in the sheet after. That's only a guess
thanks,
Bill

 

[JLGWhiz]

the "=xxx" is supposed to be the value that replaces the string represented by Ary(i), depending on the iteration it is in. But if Excel will see that as a formula because of the equal (=) symbol and when it tries to calculate a value it will error because it is an invalid formula. Of course the compiler is seeing this up front and can't make sense of it, so it throws a VBA error telling you to fix it. I can't tell you how to fix it because I don't know what values you want to replace the items in the array with. If you remove the equal sign, it should stop the error and it will put "xxx" in the cells where your Ary(i) values were.

 

[billfinn]

JLGWhiz,
What is supposed to happen is that anything that is found in the Array statement is supposed to be replaced by an error, then to is supposed to delete all the rows with errors. I made some minor changes over the weekend to correct a couple of minor issues that didn't show up in Excel 2010 but did in 2000. None of those fixed my main problem.
Right now it replaces all the Array strings with errors as it is supposed to and then just finishes. Oddly enough, if I run it a second time it deletes all the rows with errors and leaves me with the desired worksheet. I only found that out through frustration cursing and clicking in the wee hours of the morning.
It seems to completely ignore the line

Columns(i).SpecialCells(xlFormulas, xlErrors).EntireRow.Delete

the first time through but runs it the second time. I don't know nearly enough to know for sure but could it be the placement of that line causing my issue? It is right between two error statements, making me wonder if somehow it gets passed over.
I am attaching the macro again to save you from having to scroll up and down.
Thanks very much for any suggestions or input that you can provide
Bill

Sub Strip_Page_Report_Headers()    'Macro to strip out Report and Page headings to leave an Excel File with columns properly formatted.
'Timer - comment out the three lines below if you don't want the timer to run
Dim StartTime As Double
Dim MinutesElapsed As String
'Remember time when macro starts
StartTime = Timer


    'On Error GoTo ErrorHandler
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With


    Workbooks("Estimator.xls").Activate  'Go to the Estimating Tool
    Sheets("Item Master").Select      'Select the Item Master worksheet in the Estimating tool
    Selection.EntireColumn.Hidden = False
    Dim Ary As Variant
    Dim i As Long
    Ary = Array("*QUERY*", "*NVMAST*", "*info*", "*LIBRARY*", "*PRICEMSM*", "DATE*", "*TIME*", "*PAGE*", "Item", "*Cost*", "*DELETE*")
    For i = 0 To UBound(Ary)
    Range("A:H").Replace Ary(i), "=xxx", xlWhole, , False, False
    On Error Resume Next
    Columns(i).SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
    On Error GoTo 0
Next


'Add Column Labels
Rows(1).Insert Shift:=xlDown
    Range("A1").FormulaR1C1 = "ITEM"
    Range("B1").FormulaR1C1 = "DESCRIPTION"
    Range("C1").FormulaR1C1 = "COST"


  ' Set Column C as currency
Worksheets("Item Master").Columns("C").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"


Columns.AutoFit
  
'ErrorHandler:


With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
End With


'Timer end code - comment out two lines below if you don't want the timer to run
  MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
  MsgBox "This code ran successfully in " & MinutesElapsed, vbInformation




End Sub

 

[Fluff]

You've changed this part of the code

For i = 0 To UBound(Ary)
    Range("A:H").Replace Ary(i), "=xxx", xlWhole, , False, False
    On Error Resume Next
    Columns(i).SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
    On Error GoTo 0
Next

You need to change it back to what it was.

 

[billfinn]

Fluff,
Thanks much for the input. I changed that as it was causing a "Compile error: Wrong number of arguments or invalid property assignment". The debug highlighted ".Replace". There seems to be a limit to how many arguments can be listed after Ary (i). Anything over 5 arguments causes an error in Excel 2000. If there is a better way to handle that issue I would love to do so but I was unable to make it work with any more than 5 arguments.
Thanks,
Bill

 

[Fluff]

It needs to be like

    For i = 0 To UBound(Ary)
        Range("A:H").Replace Ary(i), "=xxx", xlWhole, , False, , False, False
    Next i

    For i = 1 To 15
        On Error Resume Next
        Columns(i).SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
        On Error GoTo 0
    Next i

This works in 2003, so should be ok in 2000

 

[billfinn]

Fluff,
I have tried that several times and always get the same error. I just now tried it again and got the same error. Debug is highlighting ".Replace".
There is something preventing me from using more than 5 arguments in that line.