i have the worst time trying to code a sort with the sort key......
usually i can make it work (eventually), but i'd like to understand it
better.
so can somebody explain why this is not working?!
xxxxxxxxxxxxxxx
Sub userform_initialize()

Set Bldgs = ActiveWorkbook.Worksheets(2)
Set Temp = ActiveWorkbook.Worksheets(5)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
Set MyBldgs = Temp.Range("a4:a" & MyBldgLastRow)
Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MyFindRange.Sort Key1:=Range("A4:A4"), Order1:=xlAscending,
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

For Each cell In MyBldgs
cboBldgList.AddItem cell.Value
Next cell

End Sub
xxxxxxxxxxxxxxxxxxx
it worked fine until i added the sort. i've tried making the key range
a4:a5
a4 (only)
a:a

i get the error message "sort method of range class failed"

maybe i should take out the "range" & just make it "a4"???
thanks for the assistance
(fyi everything's declared in a global mod).
susan

Key1:=Range("A4") is what you want.
--
Best wishes,

Jim


"Susan" wrote:

i have the worst time trying to code a sort with the sort key......
usually i can make it work (eventually), but i'd like to understand it
better.
so can somebody explain why this is not working?!
xxxxxxxxxxxxxxx
Sub userform_initialize()

Set Bldgs = ActiveWorkbook.Worksheets(2)
Set Temp = ActiveWorkbook.Worksheets(5)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
Set MyBldgs = Temp.Range("a4:a" & MyBldgLastRow)
Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MyFindRange.Sort Key1:=Range("A4:A4"), Order1:=xlAscending,
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

For Each cell In MyBldgs
cboBldgList.AddItem cell.Value
Next cell

End Sub
xxxxxxxxxxxxxxxxxxx
it worked fine until i added the sort. i've tried making the key range
a4:a5
a4 (only)
a:a

i get the error message "sort method of range class failed"

maybe i should take out the "range" & just make it "a4"???
thanks for the assistance
(fyi everything's declared in a global mod).
susan

i've tried making the key range:
a4:a5
a4 (only)
a:a

thanks much, jim, but i still get the same error code.......... (tried
it again just to be sure!)
susan


Jim Jackson wrote:
Key1:=Range("A4") is what you want.
--
Best wishes,

Jim


"Susan" wrote:

i have the worst time trying to code a sort with the sort key......
usually i can make it work (eventually), but i'd like to understand it
better.
so can somebody explain why this is not working?!
xxxxxxxxxxxxxxx
Sub userform_initialize()

Set Bldgs = ActiveWorkbook.Worksheets(2)
Set Temp = ActiveWorkbook.Worksheets(5)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
Set MyBldgs = Temp.Range("a4:a" & MyBldgLastRow)
Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MyFindRange.Sort Key1:=Range("A4:A4"), Order1:=xlAscending,
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

For Each cell In MyBldgs
cboBldgList.AddItem cell.Value
Next cell

End Sub
xxxxxxxxxxxxxxxxxxx
it worked fine until i added the sort. i've tried making the key range
a4:a5
a4 (only)
a:a

i get the error message "sort method of range class failed"

maybe i should take out the "range" & just make it "a4"???
thanks for the assistance
(fyi everything's declared in a global mod).
susan

I'd try:

Key1:=bldgs.Range("A4")


Susan wrote:

i have the worst time trying to code a sort with the sort key......
usually i can make it work (eventually), but i'd like to understand it
better.
so can somebody explain why this is not working?!
xxxxxxxxxxxxxxx
Sub userform_initialize()

Set Bldgs = ActiveWorkbook.Worksheets(2)
Set Temp = ActiveWorkbook.Worksheets(5)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
Set MyBldgs = Temp.Range("a4:a" & MyBldgLastRow)
Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MyFindRange.Sort Key1:=Range("A4:A4"), Order1:=xlAscending,
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

For Each cell In MyBldgs
cboBldgList.AddItem cell.Value
Next cell

End Sub
xxxxxxxxxxxxxxxxxxx
it worked fine until i added the sort. i've tried making the key range
a4:a5
a4 (only)
a:a

i get the error message "sort method of range class failed"

maybe i should take out the "range" & just make it "a4"???
thanks for the assistance
(fyi everything's declared in a global mod).
susan

--

Dave Peterson

i've tried all of the following, with the same error:

Sort Key1:=MyFindRange("A4")
Sort Key1:=MyFindRange("A4:A4") (these 2 give me type mismatch errors,
after which i tried declaring MyFindRange as Range instead of Variant,
but that didn't work either.)

Sort Key1:=MyFindRange.Range("A4")
Sort Key1:=Bldgs.Range("A4")
Sort Key1:=("A4")
Sort Key1:=Range("A4")
Sort Key1:=Range("A4:A4")
Sort Key1:=Range("A:A")
Sort Key1:=Bldgs.Range("A:A")
Sort Key1:=Bldgs.Range("A4:A4")

<sigh any other ideas?
thanks
susan


Dave Peterson wrote:
I'd try:

Key1:=bldgs.Range("A4")


Susan wrote:

i have the worst time trying to code a sort with the sort key......
usually i can make it work (eventually), but i'd like to understand it
better.
so can somebody explain why this is not working?!
xxxxxxxxxxxxxxx
Sub userform_initialize()

Set Bldgs = ActiveWorkbook.Worksheets(2)
Set Temp = ActiveWorkbook.Worksheets(5)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
Set MyBldgs = Temp.Range("a4:a" & MyBldgLastRow)
Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MyFindRange.Sort Key1:=Range("A4:A4"), Order1:=xlAscending,
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

For Each cell In MyBldgs
cboBldgList.AddItem cell.Value
Next cell

End Sub
xxxxxxxxxxxxxxxxxxx
it worked fine until i added the sort. i've tried making the key range
a4:a5
a4 (only)
a:a

i get the error message "sort method of range class failed"

maybe i should take out the "range" & just make it "a4"???
thanks for the assistance
(fyi everything's declared in a global mod).
susan

--

Dave Peterson

This didn't work?

MyFindRange.Sort Key1:=bldgs.Range("A4"), Order1:=xlAscending, _
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

I like this syntax

with myfindrange
.Sort Key1:=.columns(1), Order1:=xlAscending, _
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
end with

======
Can you sort that range manually? Do you have data in that range? Do you have
any merged cells in that range causing trouble? Is the worksheet protected?



Susan wrote:

i've tried all of the following, with the same error:

Sort Key1:=MyFindRange("A4")
Sort Key1:=MyFindRange("A4:A4") (these 2 give me type mismatch errors,
after which i tried declaring MyFindRange as Range instead of Variant,
but that didn't work either.)

Sort Key1:=MyFindRange.Range("A4")
Sort Key1:=Bldgs.Range("A4")
Sort Key1:=("A4")
Sort Key1:=Range("A4")
Sort Key1:=Range("A4:A4")
Sort Key1:=Range("A:A")
Sort Key1:=Bldgs.Range("A:A")
Sort Key1:=Bldgs.Range("A4:A4")

<sigh any other ideas?
thanks
susan

Dave Peterson wrote:
I'd try:

Key1:=bldgs.Range("A4")


Susan wrote:

i have the worst time trying to code a sort with the sort key......
usually i can make it work (eventually), but i'd like to understand it
better.
so can somebody explain why this is not working?!
xxxxxxxxxxxxxxx
Sub userform_initialize()

Set Bldgs = ActiveWorkbook.Worksheets(2)
Set Temp = ActiveWorkbook.Worksheets(5)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
Set MyBldgs = Temp.Range("a4:a" & MyBldgLastRow)
Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MyFindRange.Sort Key1:=Range("A4:A4"), Order1:=xlAscending,
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

For Each cell In MyBldgs
cboBldgList.AddItem cell.Value
Next cell

End Sub
xxxxxxxxxxxxxxxxxxx
it worked fine until i added the sort. i've tried making the key range
a4:a5
a4 (only)
a:a

i get the error message "sort method of range class failed"

maybe i should take out the "range" & just make it "a4"???
thanks for the assistance
(fyi everything's declared in a global mod).
susan

--

Dave Peterson

--

Dave Peterson

dave -
no, that didn't work - even tho it looked identical to mine, i copied &
pasted your code & commented-out mine just to be sure........
also still did not work with the with/end with syntax.

i added in
MyFindRange.Select
before to make sure it was "looking" @ the right stuff, and it is (when
stepping thru).

the worksheet has no merged cells, is not protected, is not hidden; i
can sort it manually, recorded sort looks the same as written code, and
yes there is data in the range & in the sort key.

i am getting
Run-time error 1004
Sort method of range class failed.

when i comment out the sort, the userform loads fine (just the combo
box data is not sorted).

:P aaaaarrrgggggg!
susan

Dave Peterson wrote:
This didn't work?

MyFindRange.Sort Key1:=bldgs.Range("A4"), Order1:=xlAscending, _
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

I like this syntax

with myfindrange
.Sort Key1:=.columns(1), Order1:=xlAscending, _
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
end with

======
Can you sort that range manually? Do you have data in that range? Do you have
any merged cells in that range causing trouble? Is the worksheet protected?



Susan wrote:

i've tried all of the following, with the same error:

Sort Key1:=MyFindRange("A4")
Sort Key1:=MyFindRange("A4:A4") (these 2 give me type mismatch errors,
after which i tried declaring MyFindRange as Range instead of Variant,
but that didn't work either.)

Sort Key1:=MyFindRange.Range("A4")
Sort Key1:=Bldgs.Range("A4")
Sort Key1:=("A4")
Sort Key1:=Range("A4")
Sort Key1:=Range("A4:A4")
Sort Key1:=Range("A:A")
Sort Key1:=Bldgs.Range("A:A")
Sort Key1:=Bldgs.Range("A4:A4")

<sigh any other ideas?
thanks
susan

Dave Peterson wrote:
I'd try:

Key1:=bldgs.Range("A4")


Susan wrote:

i have the worst time trying to code a sort with the sort key......
usually i can make it work (eventually), but i'd like to understand it
better.
so can somebody explain why this is not working?!
xxxxxxxxxxxxxxx
Sub userform_initialize()

Set Bldgs = ActiveWorkbook.Worksheets(2)
Set Temp = ActiveWorkbook.Worksheets(5)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
Set MyBldgs = Temp.Range("a4:a" & MyBldgLastRow)
Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MyFindRange.Sort Key1:=Range("A4:A4"), Order1:=xlAscending,
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

For Each cell In MyBldgs
cboBldgList.AddItem cell.Value
Next cell

End Sub
xxxxxxxxxxxxxxxxxxx
it worked fine until i added the sort. i've tried making the key range
a4:a5
a4 (only)
a:a

i get the error message "sort method of range class failed"

maybe i should take out the "range" & just make it "a4"???
thanks for the assistance
(fyi everything's declared in a global mod).
susan

--

Dave Peterson

--

Dave Peterson

Sorry, I don't have another guess.

Susan wrote:

dave -
no, that didn't work - even tho it looked identical to mine, i copied &
pasted your code & commented-out mine just to be sure........
also still did not work with the with/end with syntax.

i added in
MyFindRange.Select
before to make sure it was "looking" @ the right stuff, and it is (when
stepping thru).

the worksheet has no merged cells, is not protected, is not hidden; i
can sort it manually, recorded sort looks the same as written code, and
yes there is data in the range & in the sort key.

i am getting
Run-time error 1004
Sort method of range class failed.

when i comment out the sort, the userform loads fine (just the combo
box data is not sorted).

:P aaaaarrrgggggg!
susan

Dave Peterson wrote:
This didn't work?

MyFindRange.Sort Key1:=bldgs.Range("A4"), Order1:=xlAscending, _
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

I like this syntax

with myfindrange
.Sort Key1:=.columns(1), Order1:=xlAscending, _
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
end with

======
Can you sort that range manually? Do you have data in that range? Do you have
any merged cells in that range causing trouble? Is the worksheet protected?



Susan wrote:

i've tried all of the following, with the same error:

Sort Key1:=MyFindRange("A4")
Sort Key1:=MyFindRange("A4:A4") (these 2 give me type mismatch errors,
after which i tried declaring MyFindRange as Range instead of Variant,
but that didn't work either.)

Sort Key1:=MyFindRange.Range("A4")
Sort Key1:=Bldgs.Range("A4")
Sort Key1:=("A4")
Sort Key1:=Range("A4")
Sort Key1:=Range("A4:A4")
Sort Key1:=Range("A:A")
Sort Key1:=Bldgs.Range("A:A")
Sort Key1:=Bldgs.Range("A4:A4")

<sigh any other ideas?
thanks
susan

Dave Peterson wrote:
I'd try:

Key1:=bldgs.Range("A4")


Susan wrote:

i have the worst time trying to code a sort with the sort key......
usually i can make it work (eventually), but i'd like to understand it
better.
so can somebody explain why this is not working?!
xxxxxxxxxxxxxxx
Sub userform_initialize()

Set Bldgs = ActiveWorkbook.Worksheets(2)
Set Temp = ActiveWorkbook.Worksheets(5)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
Set MyBldgs = Temp.Range("a4:a" & MyBldgLastRow)
Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MyFindRange.Sort Key1:=Range("A4:A4"), Order1:=xlAscending,
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

For Each cell In MyBldgs
cboBldgList.AddItem cell.Value
Next cell

End Sub
xxxxxxxxxxxxxxxxxxx
it worked fine until i added the sort. i've tried making the key range
a4:a5
a4 (only)
a:a

i get the error message "sort method of range class failed"

maybe i should take out the "range" & just make it "a4"???
thanks for the assistance
(fyi everything's declared in a global mod).
susan

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson