Help me covert script from Excel 2003 to Excel 2000

rickyckc

Active Member
Joined
Apr 1, 2004
Messages
327
Hi all,

I have a script that works on my PC that's using Excel 2003. When my friend runs it on his PC with Excel 2000, he hits a run-time error '1004' error message that says 'Application-defined or object-defined error'. Is somebody able to help me convert and get it working on a Excel 2000 ? Below is the script

Code:
Sub MCY()
'
' Macro done on 14/08/2007 by Ricky
'
    Sheets("Sales Staff").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("Sales Order").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    Workbooks.Open Filename:= _
    "C:\MCY\SalesOrder_Raw.xls"
    Windows("SalesOrder_Raw.xls").Activate
    Cells.Select
    Selection.Copy
    Windows("Sales.xls").Activate
    Sheets("Sales Order").Select
    ActiveSheet.Paste
    Range("A1").Select
    Windows("SalesOrder_Raw.xls").Activate
    Application.DisplayAlerts = False
    ActiveWindow.Close
    Application.DisplayAlerts = True
    Range("I2").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-5]&"" ""&RC[-6]&LEFT(RC[-3],FIND(""oz"",RC[-3])+1)"
    Range("I2").Copy Destination:=Range("I2:I" & [E65536].End(xlUp).Row)
    Application.CutCopyMode = False
    Columns("C:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sales Staff").Select
    Range("D1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range("D2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    Range("D1:E17").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "A1"), Unique:=True
    Selection.ClearContents
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]&"" ""&RC[-2]"
    Range("C2").Copy Destination:=Range("C2:C" & [B65536].End(xlUp).Row)
    Application.CutCopyMode = False
    Columns("C:C").Select
    Selection.Columns.AutoFit
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    Range("C2:C7").Select
    Application.CutCopyMode = False
    On Error Resume Next
    Names("SalesStaff").Delete
    Names("Extract").Delete
    On Error GoTo 0
    ActiveWorkbook.Names.Add Name:="SalesStaff", RefersToR1C1:= _
"='Sales Staff'!R2C3:R" & Sheets("Sales Staff").Cells(Rows.Count, "C").End(xlUp).Row & "C3"
    Range("A1").Select
    Sheets("Sales Order").Select
    Range("A1").Select
    Sheets("Sales Template").Select
    Range("A1").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    Selection.ClearContents
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=SalesStaff"
        .IgnoreBlank = False
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Thanks.

Best Regards,
Ricky
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hi Andrew,

I need your help on my script again. If you refer back to my script, you will notice that I have one part whereby it sorts out repeated sales person names and then extract out unique ones. I had made a fix range which is D1:E17.

My 2 columns of first name and last name is not fixed all the time. How to change it to a dynamic selection ?

Thanks.

Best Regards,
Ricky
 
Upvote 0
try
Code:
With Range("d1:f1")
     With Range(.Cells, .End(xlDown))
          .AdvancedFilter ...........
     End With
End With
 
Upvote 0
Hi jindon,

thanks, but if you don't mind, i need you to spell it out for me as in show me the exact corrected syntax.

Thks !

Best Regards,
Ricky
 
Upvote 0
Code:
Sub MCY()
'
' Macro done on 14/08/2007 by Ricky
'
  '
   .
   .
   .
 Application.CutCopyMode = False
    Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range("D2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    With Range("D1:E17")
         With Range(.Cells,.End(xlDown))
              .AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
               "A1"), Unique:=True
          End With
    End With
    Selection.ClearContents
    Range("C2").Select
    .
    .
    .
End Sub
 
Upvote 0
Hi jindon,

Thanks, it works ! I have one question though. In my main sheet, where I have a drop-down list in A1, when I click on the arrow, it shows all the names in a limited size window and I need to scroll up or down. Is there a way to maximize that window ?

Thanks.

Best Regards,
Ricky
 
Upvote 0
To a sheet module
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0,0) <> "A1" Then Exit Sub
Columns("a").AutoFit
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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
Back
Top