Probably a very simple question...

[SamA]

Hi All,

I'm really not experienced with Macro's at all, but in my current job I've inherited a sheet that has a macro in it. The macro itself works fine when I run it in excel 2007 however when I send it out to people using excel 2003 it won't work and gives the 1004 error. It's saved in the 97-2003 compatible format and I've also tried sending it in the macro enabled format but get the same error every time.

Does this sound like a compatibility problem or a problem with the macro itself? If it's the macro itself I can contact the intern who created it to ask them to correct it but it just seems odd that it works fine in Excel 2007 but not 2003.

Any help would be greatly appreciated.

 

[Sektor]

It just means that Excel 2003 doesn't have methods used in macro.
For instance, "RemoveDuplicates" method of Range object isn't supported by Excel 2003.
The reason can be also function which doesn't exist in Excel 2003, such as SUMIFS.

 

[SamA]

Ah, thank you! So am I right in thinking that these need editing out of the Macro? Is there a list anywhere of the ones that won't work? Thanks so much!

 

[Norie]

Can you post the code?

There might be something in it that's not supported in earlier versions, but there are probably a few other things that could be causing the problem.

Even if there are things not supported it might be worth finding if they can be rewritten for all versions.

You never know they might actually be quite important parts of the macro.

 

[RoryA]

You would also be more likely to get a 438 error than a 1004 if the problem is methods/properties that don't exist in an earlier version.

 

[SamA]

Hi All,

Thanks for the help so far, I must sound like a total idiot so thanks for your patience.

I'm going to try and post the code below and use the code tags appropriately, let me know if I've made a posting faux pas and I'll correct it!

Thanks, Sam

Option Compare Text
Sub Form()
    Cats.Show
End Sub
Sub SalesCategories()
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
                
    Sheets("TWData").Visible = True
    Sheets("Shops").Visible = True
    Sheets("SSTemp").Visible = True
    
    Sheets("Shops").Select
    Cells(1, 25).Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R4C4:R200C4,""*"" & ""F"" & ""*"")"
    
    Set WB = ActiveWorkbook
    Set WS = WB.Sheets("SSTemp")
        
    For i = 4 To Sheets("Shops").Cells(1, 25).Value + 3
    If Cells(i, 4).Value = Cells(1, 1).Value Then
    WS.Copy After:=Sheets(WB.Sheets.Count)
    Sheets("Shops").Select
        
    ActiveSheet.Range(Cells(i, 4), Cells(i, 5)).Select
    Selection.Copy
    Sheets(Sheets.Count).Select
    Cells(4, 1).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveSheet.Name = Cells(4, 1).Value
    
    Cells(4, 2).Select
    Selection.Copy
    Cells(2, 1).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Cells(4, 2).Select
    Selection.ClearContents
    Cells(3, 1).Value = "Week " & Sheets("Shops").Cells(4, 11).Value
    
    Call PopulateSales
        
    Sheets(Sheets.Count).Select
    Cells(1, 1).Select
    
    Exit For
    End If
    Next
    
    Sheets("TWData").Visible = False
    Sheets("Shops").Visible = False
    Sheets("SSTemp").Visible = False
    

End Sub
Sub PopulateSales()

    Application.ScreenUpdating = False
    
    Dim i As Integer
        
    For i = 26 To 400
    If Cells(i, 1).Value <> "" Then
    Cells(i, 3).Select
    Selection.FormulaArray = _
        "=SUM(IF(TWData!R1C3:R20000C3=RC1,IF(TWData!R1C1:R20000C1=R4C1,TWData!R1C4:R20000C4,0),0))"
    ActiveCell.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Cells(i, 4).Select
    Selection.FormulaArray = _
        "=SUM(IF(TWData!R1C3:R20000C3=RC1,IF(TWData!R1C1:R20000C1=R4C1,TWData!R1C5:R20000C5,0),0))"
    ActiveCell.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Cells(i, 9).Select
    Selection.FormulaArray = _
        "=SUM(IF(TWData!R1C3:R20000C3=RC1,IF(TWData!R1C1:R20000C1=R4C1,TWData!R1C6:R20000C6,0),0))"
    ActiveCell.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Cells(i, 10).Select
    Selection.FormulaArray = _
        "=SUM(IF(TWData!R1C3:R20000C3=RC1,IF(TWData!R1C1:R20000C1=R4C1,TWData!R1C7:R20000C7,0),0))"
    ActiveCell.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
    End If
    Next
    
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Application.CutCopyMode = False
            
    Cells(4, 1).Select
          
End Sub

 

[SamA]

Can anyone help with the above? Would be really appreciated! Thanks, Sam

 

[RoryA]

It would be helpful if you told us which line causes the error.

 

[SamA]

How do I find that out? I'm really not very experienced with Macros and on my computer it works so I'm not sure where to find the error. Sorry!

 

[RoryA]

Do you get an option to Debug when the error appears? If so, pressing that should highlight a line of code.