Sort key
 

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

Sort key
 

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

Sort key
 

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

Sort key
 

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

Sort key
 

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

Sort key
 

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

Sort key
 

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

Sort key
 

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

Sort key
 

:) thank you for trying!
it looks like i have to use
Bldgs.Activate
first, in order to be able to sort a range on that sheet.
sheesh!
susan



Dave Peterson wrote:
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

Sort key
 

I've never had to activate the sheet first.

If you get rid of some of the other stuff, what happens:

Sub userform_initialize()

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

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

msgbox myfindrange.address(external:=true)

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

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

End Sub

Do you still have a problem?

And are you sure you checked the correct worksheet (worksheets(2))?



Susan wrote:

:) thank you for trying!
it looks like i have to use
Bldgs.Activate
first, in order to be able to sort a range on that sheet.
sheesh!
susan

Dave Peterson wrote:
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

--

Dave Peterson

Sort key
 

still the same error with stuff commented out (reprinted below as
separate sub)...... (i like that msgbox showing the variable, tho -
easier than a "watch"!).

using File Name: EXCEL.EXE
Display Name: Microsoft Office 2000
Description: Microsoft Excel for Windows
File Version: 9.0.3822

on Microsoft(R) Windows (R) 2000 Operating System.

vba project tree =
sheet1 (sheet1)
sheet2 (Bldgs)
sheet3 (Order)
etc..........
range shows correctly on correct sheet & msgbox says correct range &
sheet.

i don't know why it won't work, but adding in the .activate in the
initialization sub does make it work.......... but it doesn't make it
work here.................
xxxxxxxxxxxxxxxxxxx
Public Sub sort()

Set Bldgs = ActiveWorkbook.Worksheets(2)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
MsgBox MyBldgLastRow

Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MsgBox MyFindRange.Address(external:=True)

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

End Sub
xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
susan


Dave Peterson wrote:
I've never had to activate the sheet first.

If you get rid of some of the other stuff, what happens:

Sub userform_initialize()

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

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

msgbox myfindrange.address(external:=true)

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

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

End Sub

Do you still have a problem?

And are you sure you checked the correct worksheet (worksheets(2))?



Susan wrote:

:) thank you for trying!
it looks like i have to use
Bldgs.Activate
first, in order to be able to sort a range on that sheet.
sheesh!
susan

Dave Peterson wrote:
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

--

Dave Peterson

Sort key
 

alright now, something's rotten in denmark.

another userform, which is supposed to select that worksheet to add a
new building, is also not working correctly (when it was). it adds the
information to the cover worksheet, instead of where it is supposed to.
i only have one workbook open.................
isn't this sufficient for declaring a page?

(this is in the global declarations module)
Public Bldgs As Worksheet

& then in each individual module/userform sub where it's needed, i've
got
Set Bldgs = ActiveWorkbook.Worksheets(2)

and it's definitely the #2 worksheet....... i've looked @ the
properties in the VBA editor.....

is there something different i need to indicate a particular sheet?????
susan


Susan wrote:
still the same error with stuff commented out (reprinted below as
separate sub)...... (i like that msgbox showing the variable, tho -
easier than a "watch"!).

using File Name: EXCEL.EXE
Display Name: Microsoft Office 2000
Description: Microsoft Excel for Windows
File Version: 9.0.3822

on Microsoft(R) Windows (R) 2000 Operating System.

vba project tree =
sheet1 (sheet1)
sheet2 (Bldgs)
sheet3 (Order)
etc..........
range shows correctly on correct sheet & msgbox says correct range &
sheet.

i don't know why it won't work, but adding in the .activate in the
initialization sub does make it work.......... but it doesn't make it
work here.................
xxxxxxxxxxxxxxxxxxx
Public Sub sort()

Set Bldgs = ActiveWorkbook.Worksheets(2)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
MsgBox MyBldgLastRow

Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MsgBox MyFindRange.Address(external:=True)

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

End Sub
xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
susan


Dave Peterson wrote:
I've never had to activate the sheet first.

If you get rid of some of the other stuff, what happens:

Sub userform_initialize()

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

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

msgbox myfindrange.address(external:=true)

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

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

End Sub

Do you still have a problem?

And are you sure you checked the correct worksheet (worksheets(2))?



Susan wrote:

:) thank you for trying!
it looks like i have to use
Bldgs.Activate
first, in order to be able to sort a range on that sheet.
sheesh!
susan

Dave Peterson wrote:
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

--

Dave Peterson

Sort key
 

Sub sheet_name()

Dim SHName As String
SHName = Worksheets(1).Name
MsgBox "the nameof the first worksheet is" & SHName, vbOKOnly

SHName = Worksheets(2).Name
MsgBox "the nameof the second worksheet is" & SHName, vbOKOnly

SHName = Worksheets(3).Name
MsgBox "the nameof the third worksheet is" & SHName, vbOKOnly

SHName = Worksheets(4).Name
MsgBox "the nameof the fourth worksheet is" & SHName, vbOKOnly

SHName = Worksheets(5).Name
MsgBox "the nameof the fifth worksheet is" & SHName, vbOKOnly

End Sub
xxxxxxxxxxxxxxxxxxxxx
using this tells me that the 2nd worksheet is indeed "bldgs".......
susan
Susan wrote:
alright now, something's rotten in denmark.

another userform, which is supposed to select that worksheet to add a
new building, is also not working correctly (when it was). it adds the
information to the cover worksheet, instead of where it is supposed to.
i only have one workbook open.................
isn't this sufficient for declaring a page?

(this is in the global declarations module)
Public Bldgs As Worksheet

& then in each individual module/userform sub where it's needed, i've
got
Set Bldgs = ActiveWorkbook.Worksheets(2)

and it's definitely the #2 worksheet....... i've looked @ the
properties in the VBA editor.....

is there something different i need to indicate a particular sheet?????
susan


Susan wrote:
still the same error with stuff commented out (reprinted below as
separate sub)...... (i like that msgbox showing the variable, tho -
easier than a "watch"!).

using File Name: EXCEL.EXE
Display Name: Microsoft Office 2000
Description: Microsoft Excel for Windows
File Version: 9.0.3822

on Microsoft(R) Windows (R) 2000 Operating System.

vba project tree =
sheet1 (sheet1)
sheet2 (Bldgs)
sheet3 (Order)
etc..........
range shows correctly on correct sheet & msgbox says correct range &
sheet.

i don't know why it won't work, but adding in the .activate in the
initialization sub does make it work.......... but it doesn't make it
work here.................
xxxxxxxxxxxxxxxxxxx
Public Sub sort()

Set Bldgs = ActiveWorkbook.Worksheets(2)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
MsgBox MyBldgLastRow

Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MsgBox MyFindRange.Address(external:=True)

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

End Sub
xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
susan


Dave Peterson wrote:
I've never had to activate the sheet first.

If you get rid of some of the other stuff, what happens:

Sub userform_initialize()

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

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

msgbox myfindrange.address(external:=true)

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

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

End Sub

Do you still have a problem?

And are you sure you checked the correct worksheet (worksheets(2))?



Susan wrote:

:) thank you for trying!
it looks like i have to use
Bldgs.Activate
first, in order to be able to sort a range on that sheet.
sheesh!
susan

Dave Peterson wrote:
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

--

Dave Peterson

Sort key
 

worksheets(2) is the second worksheet from the left when you're looking at the
workbook in Excel.

If the name of the worksheet never changes, you could use:
set bldgs = Activeworkbook.worksheets("bldgs")

And you didn't say what you saw in the msgbox's either.



Susan wrote:

still the same error with stuff commented out (reprinted below as
separate sub)...... (i like that msgbox showing the variable, tho -
easier than a "watch"!).

using File Name: EXCEL.EXE
Display Name: Microsoft Office 2000
Description: Microsoft Excel for Windows
File Version: 9.0.3822

on Microsoft(R) Windows (R) 2000 Operating System.

vba project tree =
sheet1 (sheet1)
sheet2 (Bldgs)
sheet3 (Order)
etc..........
range shows correctly on correct sheet & msgbox says correct range &
sheet.

i don't know why it won't work, but adding in the .activate in the
initialization sub does make it work.......... but it doesn't make it
work here.................
xxxxxxxxxxxxxxxxxxx
Public Sub sort()

Set Bldgs = ActiveWorkbook.Worksheets(2)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
MsgBox MyBldgLastRow

Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MsgBox MyFindRange.Address(external:=True)

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

End Sub
xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
susan

Dave Peterson wrote:
I've never had to activate the sheet first.

If you get rid of some of the other stuff, what happens:

Sub userform_initialize()

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

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

msgbox myfindrange.address(external:=true)

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

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

End Sub

Do you still have a problem?

And are you sure you checked the correct worksheet (worksheets(2))?



Susan wrote:

:) thank you for trying!
it looks like i have to use
Bldgs.Activate
first, in order to be able to sort a range on that sheet.
sheesh!
susan

Dave Peterson wrote:
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

--

Dave Peterson

--

Dave Peterson

Sort key
 

Be careful. The second worksheet isn't determined by the list in the project
explorer.

Susan wrote:

alright now, something's rotten in denmark.

another userform, which is supposed to select that worksheet to add a
new building, is also not working correctly (when it was). it adds the
information to the cover worksheet, instead of where it is supposed to.
i only have one workbook open.................
isn't this sufficient for declaring a page?

(this is in the global declarations module)
Public Bldgs As Worksheet

& then in each individual module/userform sub where it's needed, i've
got
Set Bldgs = ActiveWorkbook.Worksheets(2)

and it's definitely the #2 worksheet....... i've looked @ the
properties in the VBA editor.....

is there something different i need to indicate a particular sheet?????
susan

Susan wrote:
still the same error with stuff commented out (reprinted below as
separate sub)...... (i like that msgbox showing the variable, tho -
easier than a "watch"!).

using File Name: EXCEL.EXE
Display Name: Microsoft Office 2000
Description: Microsoft Excel for Windows
File Version: 9.0.3822

on Microsoft(R) Windows (R) 2000 Operating System.

vba project tree =
sheet1 (sheet1)
sheet2 (Bldgs)
sheet3 (Order)
etc..........
range shows correctly on correct sheet & msgbox says correct range &
sheet.

i don't know why it won't work, but adding in the .activate in the
initialization sub does make it work.......... but it doesn't make it
work here.................
xxxxxxxxxxxxxxxxxxx
Public Sub sort()

Set Bldgs = ActiveWorkbook.Worksheets(2)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
MsgBox MyBldgLastRow

Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MsgBox MyFindRange.Address(external:=True)

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

End Sub
xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
susan


Dave Peterson wrote:
I've never had to activate the sheet first.

If you get rid of some of the other stuff, what happens:

Sub userform_initialize()

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

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

msgbox myfindrange.address(external:=true)

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

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

End Sub

Do you still have a problem?

And are you sure you checked the correct worksheet (worksheets(2))?



Susan wrote:

:) thank you for trying!
it looks like i have to use
Bldgs.Activate
first, in order to be able to sort a range on that sheet.
sheesh!
susan

Dave Peterson wrote:
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

--

Dave Peterson

--

Dave Peterson

Sort key
 

I'd still use the name of the worksheet:

Set Bldgs = ActiveWorkbook.Worksheets("Bldgs")



Susan wrote:

Sub sheet_name()

Dim SHName As String
SHName = Worksheets(1).Name
MsgBox "the nameof the first worksheet is" & SHName, vbOKOnly

SHName = Worksheets(2).Name
MsgBox "the nameof the second worksheet is" & SHName, vbOKOnly

SHName = Worksheets(3).Name
MsgBox "the nameof the third worksheet is" & SHName, vbOKOnly

SHName = Worksheets(4).Name
MsgBox "the nameof the fourth worksheet is" & SHName, vbOKOnly

SHName = Worksheets(5).Name
MsgBox "the nameof the fifth worksheet is" & SHName, vbOKOnly

End Sub
xxxxxxxxxxxxxxxxxxxxx
using this tells me that the 2nd worksheet is indeed "bldgs".......
susan
Susan wrote:
alright now, something's rotten in denmark.

another userform, which is supposed to select that worksheet to add a
new building, is also not working correctly (when it was). it adds the
information to the cover worksheet, instead of where it is supposed to.
i only have one workbook open.................
isn't this sufficient for declaring a page?

(this is in the global declarations module)
Public Bldgs As Worksheet

& then in each individual module/userform sub where it's needed, i've
got
Set Bldgs = ActiveWorkbook.Worksheets(2)

and it's definitely the #2 worksheet....... i've looked @ the
properties in the VBA editor.....

is there something different i need to indicate a particular sheet?????
susan


Susan wrote:
still the same error with stuff commented out (reprinted below as
separate sub)...... (i like that msgbox showing the variable, tho -
easier than a "watch"!).

using File Name: EXCEL.EXE
Display Name: Microsoft Office 2000
Description: Microsoft Excel for Windows
File Version: 9.0.3822

on Microsoft(R) Windows (R) 2000 Operating System.

vba project tree =
sheet1 (sheet1)
sheet2 (Bldgs)
sheet3 (Order)
etc..........
range shows correctly on correct sheet & msgbox says correct range &
sheet.

i don't know why it won't work, but adding in the .activate in the
initialization sub does make it work.......... but it doesn't make it
work here.................
xxxxxxxxxxxxxxxxxxx
Public Sub sort()

Set Bldgs = ActiveWorkbook.Worksheets(2)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
MsgBox MyBldgLastRow

Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MsgBox MyFindRange.Address(external:=True)

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

End Sub
xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
susan


Dave Peterson wrote:
I've never had to activate the sheet first.

If you get rid of some of the other stuff, what happens:

Sub userform_initialize()

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

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

msgbox myfindrange.address(external:=true)

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

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

End Sub

Do you still have a problem?

And are you sure you checked the correct worksheet (worksheets(2))?



Susan wrote:

:) thank you for trying!
it looks like i have to use
Bldgs.Activate
first, in order to be able to sort a range on that sheet.
sheesh!
susan

Dave Peterson wrote:
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

--

Dave Peterson

--

Dave Peterson

Sort key
 

(sigh).
it is the 2nd worksheet from the left. the name of the worksheet never
changes.
the 1st msgbox said "10", which is correct, that is the current "last"
row.
the 2nd msgbox said "[correctnameofspreadsheet.xls]Bldgs!$A$4:$E$10",
which is also correct.
changing it to "Set Bldgs = ActiveWorkbook.Worksheets("Bldgs")" didn't
help.
i think it's time for a day off!!!!!
:)
susan

Dave Peterson wrote:
worksheets(2) is the second worksheet from the left when you're looking at the
workbook in Excel.

If the name of the worksheet never changes, you could use:
set bldgs = Activeworkbook.worksheets("bldgs")

And you didn't say what you saw in the msgbox's either.



Susan wrote:

still the same error with stuff commented out (reprinted below as
separate sub)...... (i like that msgbox showing the variable, tho -
easier than a "watch"!).

using File Name: EXCEL.EXE
Display Name: Microsoft Office 2000
Description: Microsoft Excel for Windows
File Version: 9.0.3822

on Microsoft(R) Windows (R) 2000 Operating System.

vba project tree =
sheet1 (sheet1)
sheet2 (Bldgs)
sheet3 (Order)
etc..........
range shows correctly on correct sheet & msgbox says correct range &
sheet.

i don't know why it won't work, but adding in the .activate in the
initialization sub does make it work.......... but it doesn't make it
work here.................
xxxxxxxxxxxxxxxxxxx
Public Sub sort()

Set Bldgs = ActiveWorkbook.Worksheets(2)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
MsgBox MyBldgLastRow

Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MsgBox MyFindRange.Address(external:=True)

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

End Sub
xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
susan

Dave Peterson wrote:
I've never had to activate the sheet first.

If you get rid of some of the other stuff, what happens:

Sub userform_initialize()

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

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

msgbox myfindrange.address(external:=true)

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

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

End Sub

Do you still have a problem?

And are you sure you checked the correct worksheet (worksheets(2))?



Susan wrote:

:) thank you for trying!
it looks like i have to use
Bldgs.Activate
first, in order to be able to sort a range on that sheet.
sheesh!
susan

Dave Peterson wrote:
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

--

Dave Peterson

--

Dave Peterson