Code won't work in Excel 2003, but will in 2007/2010

[Mak2145]

I wrote a program in Excel 2010 and it works fine, but this code will not work in 2003. I did check the object references in vba and unchecked the Missing ones, which was Outlook. I am getting errors such as "Can't find project or Library" and Mismatch type among others. It all seems to circle around the 2 lines of code that I highlighted below in red.

Sub SubOpsListing()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Sheets("Standard Output").Select
x = 0
Range("A18:A10000").ClearContents

For Each C In Range("SUB_OPS_USED")

    If C.Value = "YES" Then
        UsedSubOp = C.Offset(0, -2).Value
        Range("SubOpListingStart").Offset(x, 0).Value = UsedSubOp
        x = x + 1
    
    End If
    
Next C

Range("A18:A10000").Select
ActiveWorkbook.Worksheets("Standard Output").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Standard Output").Sort.SortFields.Add Key:=Range( _
        "A18"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    
With ActiveWorkbook.Worksheets("Standard Output").Sort
        .SetRange Range("A18:A10000")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With

Range("A1").Select

End Sub

[abousetta]

If the upper range of Range("SUB_OPS_USED") is more than 65,000 then it could be it. ALso you are not using any declarations. Try declaring c as a variant and see what happens.

[Paul]

Actually, declare C as Range, since that's what you're using it for.

[Mak2145]

Sup_Ops_Used is 120. I declared C as Range and got a type mismatch on the "If C.Value = "YES" Then" line

[Paul]

What is the exact range of SUB_OPS_USED? If it's to the left of column C I think you'll have an issue as you're offsetting -2. Longshot.. but try getting rid of the word SUB in your variables and ranges.

If not, can you post a copy of your workbook so someone else with Excel 2003 can look? (Not me, unfortunately..)

[Mak2145]

Here is a basic version

[Paul]

For 2007+ you may want to use the code like this:

Sub SubOpsListing()
Dim x As Long, C As Range, UsedSubOp As Variant
x = 0

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

With Sheets("Standard Output")
    .Range("A18:A10000").ClearContents
    For Each C In Sheets("Data").Range("SUB_OPS_USED")
        If C.Value = "YES" Then
            UsedSubOp = C.Offset(0, -1).Value
            .Range("SubOpListingStart").Offset(x, 0).Value = UsedSubOp
            x = x + 1
        End If
    Next C
    
    .Range("A18:A10000").Select
    With .Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A18"), SortOn:=xlSortOnValues, _
            Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A18:A10000")
        .Header = xlNo
        .Orientation = xlTopToBottom
        .Apply
    End With
    .Range("A1").Select
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

All variables are Dim'd, and Range objects are qualified to their proper worksheets. What might be the issue in 2003 is the Sort part of the code. Try recording a macro of sorting a column in Excel 2003 and you'll see what the code should be. (It's different - and I believe you can use the 2003 Sort code in 2007+, but not vice-versa.)

Also note that your sorting starts in row 18 (A18), however the data starts in A2. So the first 16 rows don't get sorted with the rest of the data.

[royUK]

Check the sort code part, have you debugged it in 2003. The sort method is different in Excel 2003, try recording the same actions & compare the code.

[Mak2145]

I recorded the sort and replaced. Still no go. It still errors to the "If C.Value = "YES" Then" line. Also Paul, yep I was minimizing the file and forgot to change the range from A18. No big deal tho

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.


On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.


You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.


To upload a file from your computer, click the 'Browse' button and locate the file.


To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.


Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[abousetta]

Hi Roy, the OP provided one in post #6. It runs fine with Paul's code. I will try to run it on a laptop at work that runs 2003 and try to debug.

[Mak2145]

I posted an example a few posts up.

[Paul]

Since we need to test in 2003, can you re-post a copy of the file in .xls format? I know there are converters for reading newer files in 2003, but we should probably start with a clean slate.

[jaslake]

Hi Mak2145
Change your Sort Routine

Sub SubOpsListing()
Dim x As Long
Dim C As Range
Dim UsedSubOp As String
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Sheets("Standard Output").Select
x = 0
Range("A18:A10000").ClearContents

For Each C In Range("SUB_OPS_USED")

    If C.Value = "YES" Then
        UsedSubOp = C.Offset(0, -1).Value
        Range("SubOpListingStart").Offset(x, 0).Value = UsedSubOp
        x = x + 1
    
    End If
    
Next C

Range("A1:A10000").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'Range("A18:A10000").Select
'ActiveWorkbook.Worksheets("Standard Output").Sort.SortFields.Clear
'ActiveWorkbook.Worksheets("Standard Output").Sort.SortFields.Add Key:=Range( _
'        "A18"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
'        xlSortNormal
'
'With ActiveWorkbook.Worksheets("Standard Output").Sort
'        .SetRange Range("A18:A10000")
'        .Header = xlNo
'        .MatchCase = False
'        .Orientation = xlTopToBottom
'        .SortMethod = xlPinYin
'        .Apply
'End With

Range("A1").Select
    Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Also, you could probably use AutoFilter for the first part of the routine rather than a loop and VLookUps. Let me know of issues.

[JosephP]

If you're getting a type mismatch on a cell value test I'd bet you have an error in that cell.

[Mak2145]

Thanks for all of the help. The problem is I didn't know IFERROR statments don't work in 2003. I have found the macro to let them run in 2003, but I am having a slight problem. All of the iferror statements have a _xlfn. attached to them now. I can do a manual find and remove and the program runs fine. I am trying to create a macro to do this for me, but the problem is the macro won't search formulas like doing it manually can. I have this base code, but am not sure what do add to accomplish this.

    Cells.Replace What:="_xlfn.", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

[JosephP]

You can't simply remove the xlfn part as iferror will not work in 2003. You need to switch to if(iserror( formulas instead. You would find it easier to build the project in 2003 I reckon.

[Mak2145]

You can't simply remove the xlfn part as iferror will not work in 2003. You need to switch to if(iserror( formulas instead. You would find it easier to build the project in 2003 I reckon.

I am using this macro for the iferrors to run in 2003.

Function IFERROR(ToEvaluate As Variant, Default As Variant) As Variant
'Duplicates Excel 2007 functionality (UDF)
    If IsArray(ToEvaluate) Then
        IFERROR = IIf(IsError(ToEvaluate(1)), Default, ToEvaluate)
    Else
        IFERROR = IIf(IsError(ToEvaluate), Default, ToEvaluate)
    End If
End Function

If I do a manual find and remove the xlfn, this works great and the program runs fine after that. I just need a macro to automatically find and replace the _xlfn. within the formula.

[JosephP]

Your code works fine in your sample workbook in 2003 - replace will only work on formulas since you can't replace the result of a formula without changing the formula. I still think it would be simpler to use "IF(ISERROR(" but I don't see why you can't do the replace manually once anyway.

[Mak2145]

That example book i didnt include any of the iferror statements. I originally designed this in excel 2010. I completed it and found there are still a couple of users with 2003 that need this program to run on thier computer. The reason i need it to be automatic is because i will be distributing this to different people and most are very basic units.

[jaslake]

Hi Mak2145
As I previously suggested

you could probably use AutoFilter for the first part of the routine rather than a loop and VLookUps

This code does that and eliminates the lookups. It's been tested in Excel 2007 and Excel 2000 and appears to do as you require.

Sub SubOpsListing()
    Dim LR As Long
    Dim rng1 As Range
    Dim rng2 As Range
    Dim myRng As Range
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    Sheets("Standard Output").Select
    Range("A1:B10000").ClearContents
    With Sheet6
        LR = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row

        .Range("A3:C" & LR).AutoFilter Field:=2, Criteria1:="YES"

        Set rng1 = .Range("A4:A" & LR).SpecialCells(xlCellTypeVisible)
        Set rng2 = .Range("C4:C" & LR).SpecialCells(xlCellTypeVisible)
        Set myRng = Union(rng1, rng2)
        myRng.Copy
        Sheet2.Range("A2").PasteSpecial xlPasteValues
        .AutoFilterMode = False
    End With

    Range("A1:B10000").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Range("A1").Select

       Application.CutCopyMode = False    
       Application.Calculation = xlCalculationAutomatic    
       Application.ScreenUpdating = True
End Sub

[JosephP]

The workbook in post 6 does have IFERROR formulas in it.

Why don't you just replace the formulas before distributing it - then it will work for everyone.