Application-Defined or Object-Defined Error on simple code

[[email protected]]

I have the following code that produces an Application defined or
object defined error:

Sub SortStyleList()
Dim SortRange As Range

With Sheets("Cost&Margin")
SortRange = .Range(.Range("C1002"), Range("c1002").End(xlDown))
UnprotectAll
SortRange.Sort Key1:=.Range("C1002")
ProtectAll
End With

End Sub


But the following code works flawlessly.

Range("C1002", Range("C1002").End(xlDown)).Select
Selection.Sort Key1:=Range("C1002")

I do not want to select the range. What am I doing wrong?

Thanks,

Brent

[Jim Cone]

Brent,

Set SortRange = .Range(.Range("C1002"), Range("c1002").End(xlDown))

Regards,
Jim Cone
San Francisco, USA


<[email protected]> wrote in message
news:[email protected]...
> I have the following code that produces an Application defined or
> object defined error:
>
> Sub SortStyleList()
> Dim SortRange As Range
>
> With Sheets("Cost&Margin")
> SortRange = .Range(.Range("C1002"), Range("c1002").End(xlDown))
> UnprotectAll
> SortRange.Sort Key1:=.Range("C1002")
> ProtectAll
> End With
> End Sub
>
> But the following code works flawlessly.
>
> Range("C1002", Range("C1002").End(xlDown)).Select
> Selection.Sort Key1:=Range("C1002")
>
> I do not want to select the range. What am I doing wrong?
> Thanks,
> Brent

[[email protected]]

Thanks Jim, that got me over the first hurdle. Now I get a Sort Method
of Range Class Failed at:

SortRange.Sort Key1:=.Range("C1002")

Any other insights?

Thanks,

Brent

Jim Cone wrote:
> Brent,
>
> Set SortRange = .Range(.Range("C1002"), Range("c1002").End(xlDown))
>
> Regards,
> Jim Cone
> San Francisco, USA
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > I have the following code that produces an Application defined or
> > object defined error:
> >
> > Sub SortStyleList()
> > Dim SortRange As Range
> >
> > With Sheets("Cost&Margin")
> > SortRange = .Range(.Range("C1002"), Range("c1002").End(xlDown))
> > UnprotectAll
> > SortRange.Sort Key1:=.Range("C1002")
> > ProtectAll
> > End With
> > End Sub
> >
> > But the following code works flawlessly.
> >
> > Range("C1002", Range("C1002").End(xlDown)).Select
> > Selection.Sort Key1:=Range("C1002")
> >
> > I do not want to select the range. What am I doing wrong?
> > Thanks,
> > Brent

[Jim Cone]

Brent,

It worked for me with the two "protect" lines commented out.

Regards,
Jim Cone
San Francisco, USA

<[email protected]> wrote in message
news:[email protected]...
> Thanks Jim, that got me over the first hurdle. Now I get a Sort Method
> of Range Class Failed at:
> SortRange.Sort Key1:=.Range("C1002")
> Any other insights?
> Thanks,
> Brent



> Jim Cone wrote:
> > Brent,
> >
> > Set SortRange = .Range(.Range("C1002"), Range("c1002").End(xlDown))
> >
> > Regards,
> > Jim Cone
> > San Francisco, USA


> > <[email protected]> wrote in message
> > news:[email protected]...
> > > I have the following code that produces an Application defined or
> > > object defined error:
> > > Sub SortStyleList()
> > > Dim SortRange As Range
> > > With Sheets("Cost&Margin")
> > > SortRange = .Range(.Range("C1002"), Range("c1002").End(xlDown))
> > > UnprotectAll
> > > SortRange.Sort Key1:=.Range("C1002")
> > > ProtectAll
> > > End With
> > > End Sub
> > > But the following code works flawlessly.
> > > Range("C1002", Range("C1002").End(xlDown)).Select
> > > Selection.Sort Key1:=Range("C1002")
> > > I do not want to select the range. What am I doing wrong?
> > > Thanks,
> > > Brent

[Myrna Larson]

Sounds to me like Cost&Margin isn't the active sheet when the code runs. If
that's true, you have a missing period.

Change this line

Set SortRange = .Range(.Range("C1002"), Range("c1002").End(xlDown))


to

Set SortRange = .Range(.Range("C1002"), .Range("c1002").End(xlDown))
^

On 17 Feb 2005 14:47:11 -0800, "[email protected]" <[email protected]>
wrote:

>Thanks Jim, that got me over the first hurdle. Now I get a Sort Method
>of Range Class Failed at:
>
>SortRange.Sort Key1:=.Range("C1002")
>
>Any other insights?
>
>Thanks,
>
>Brent
>
>Jim Cone wrote:
>> Brent,
>>
>> Set SortRange = .Range(.Range("C1002"), Range("c1002").End(xlDown))
>>
>> Regards,
>> Jim Cone
>> San Francisco, USA
>>
>>
>> <[email protected]> wrote in message
>> news:[email protected]...
>> > I have the following code that produces an Application defined or
>> > object defined error:
>> >
>> > Sub SortStyleList()
>> > Dim SortRange As Range
>> >
>> > With Sheets("Cost&Margin")
>> > SortRange = .Range(.Range("C1002"), Range("c1002").End(xlDown))
>> > UnprotectAll
>> > SortRange.Sort Key1:=.Range("C1002")
>> > ProtectAll
>> > End With
>> > End Sub
>> >
>> > But the following code works flawlessly.
>> >
>> > Range("C1002", Range("C1002").End(xlDown)).Select
>> > Selection.Sort Key1:=Range("C1002")
>> >
>> > I do not want to select the range. What am I doing wrong?
>> > Thanks,
>> > Brent

[Jim Cone]

Myrna,

"." pesky little devils<g>

Regards,
Jim Cone


"Myrna Larson" <[email protected]> wrote in message news:[email protected]...
> Sounds to me like Cost&Margin isn't the active sheet when the code runs. If
> that's true, you have a missing period.
>
> Change this line
>
> Set SortRange = .Range(.Range("C1002"), Range("c1002").End(xlDown))
>
> to
>
> Set SortRange = .Range(.Range("C1002"), .Range("c1002").End(xlDown))
^

[[email protected]]

I have added that pesky little period and I still am getting "Sort
method of range class failed" I seem to have run into similar
situations like this where the code works for other prople but bombs
out on me.

Here is my code:

Sub SortStyleList()
Dim SortRange As Range

With Sheets("Cost&Margin")
Set SortRange = .Range(.Range("C1002"),
..Range("C1002").End(xlDown))
SortRange.Sort Key1:=.Range("C1002")
End With

End Sub

I get the error at the SortRange.Sort Key1:=.Range("C1002") line.

Thanks,

Brent