Sort key

[Susan]

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

 

[Guest]

Key1:=Range("A4") is what you want.

 

[Susan]

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

 

[Dave Peterson]

I'd try:

Key1:=bldgs.Range("A4")

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

 

[Susan]

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]

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]

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).

aaaaarrrgggggg!

susan

 

[Dave Peterson]

Sorry, I don't have another guess.

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).

aaaaarrrgggggg!

susan

 

[Susan]

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]

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))?

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

 

[Susan]

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

 

[Susan]

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

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

 

[Susan]

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".......

 

[Dave Peterson]

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.

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]

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

 

[Dave Peterson]

I'd still use the name of the worksheet:

Set Bldgs = ActiveWorkbook.Worksheets("Bldgs")

 

[Susan]

(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