none
Application defined or Object defined error

    Question

  • I am writing following lines of code in the click event of button which is on Sheet2. but this gives me object defined or Application defined error.if i remove "Worksheets("Sheet1")" then it will work correctly but select Sheet2's range but i want range from Sheet1.

    so help with this.

    Worksheets("Sheet1").Range(Cells(2, 1), Cells(lastrow, 5)).Sort _
            Key1:=Range("E2"), Order1:=xlDescending

    Friday, June 8, 2012 6:17 AM

Answers

  • Explanation of the problem is to describe what is being referenced in each section of your code.

    Worksheets("Sheet1").Range       (Referenced correctly)

    Cells(2, 1), Cells(lastrow, 5))       (Cells not tied to a worksheet so it is referencing the active sheet instead of Sheet1.)

    Range("E2")            (Range is not tied to a worksheet so it is referencing the active sheet instead of Sheet1.)

    Correct syntax in your code would be the following

    Worksheets("Sheet1").Range(Sheets("Sheet1").Cells(2, 1), _
                Worksheets("Sheet1").Cells(lastRow, 5)).Sort _
                Key1:=Worksheets("Sheet1").Range("E2"), Order1:=xlDescending

    However, it is better to use With / End With as per the following example so that only the dot is required to tie the objects (Range or Cells) to the worksheet.

    With Worksheets("Sheet1")
        .Range(.Cells(2, 1), .Cells(lastRow, 5)).Sort _
             Key1:=.Range("E2"), Order1:=xlDescending
    End With    


    Regards, OssieMac

    Friday, June 8, 2012 7:31 AM
  • You have to specify that ALL ranges mentioned in the code belong to Sheet1. You could use a With ... End With construction:

        With Worksheets("Sheet1")
            .Range(.Cells(2, 1), .Cells(lastrow, 5)).Sort _
                Key1:=.Range("E2"), Order1:=xlDescending
        End With

    Note the use of .Range and .Cells instead of just Range and Cells.

    Regards, Hans Vogelaar

    Friday, June 8, 2012 9:09 AM

All replies

  • I am writing following lines of code in the click event of button which is on Sheet2. but this gives me object defined or Application defined error.if i remove "Worksheets("Sheet1")" then it will work correctly but select Sheet2's range but i want range from Sheet1.

    so help with this.

    Worksheets("Sheet1").Range(Cells(2, 1), Cells(lastrow, 5)).Sort _
            Key1:=Range("E2"), Order1:=xlDescending

    Friday, June 8, 2012 6:06 AM
  • Explanation of the problem is to describe what is being referenced in each section of your code.

    Worksheets("Sheet1").Range       (Referenced correctly)

    Cells(2, 1), Cells(lastrow, 5))       (Cells not tied to a worksheet so it is referencing the active sheet instead of Sheet1.)

    Range("E2")            (Range is not tied to a worksheet so it is referencing the active sheet instead of Sheet1.)

    Correct syntax in your code would be the following

    Worksheets("Sheet1").Range(Sheets("Sheet1").Cells(2, 1), _
                Worksheets("Sheet1").Cells(lastRow, 5)).Sort _
                Key1:=Worksheets("Sheet1").Range("E2"), Order1:=xlDescending

    However, it is better to use With / End With as per the following example so that only the dot is required to tie the objects (Range or Cells) to the worksheet.

    With Worksheets("Sheet1")
        .Range(.Cells(2, 1), .Cells(lastRow, 5)).Sort _
             Key1:=.Range("E2"), Order1:=xlDescending
    End With    


    Regards, OssieMac

    Friday, June 8, 2012 7:31 AM
  • You have to specify that ALL ranges mentioned in the code belong to Sheet1. You could use a With ... End With construction:

        With Worksheets("Sheet1")
            .Range(.Cells(2, 1), .Cells(lastrow, 5)).Sort _
                Key1:=.Range("E2"), Order1:=xlDescending
        End With

    Note the use of .Range and .Cells instead of just Range and Cells.

    Regards, Hans Vogelaar

    Friday, June 8, 2012 9:09 AM