(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