Range("Defined Name").Sort

[michael.blaustein]

I am getting a "Subscript out of range" error trying to run a simple
bit of code. This is the code, essentially:

Range("valJobCostEntryList").Sort _
Key1:=Worksheets("wksJobCostInput").Range("E8"), _
Key2:=Worksheets("wksJobCostInput").Range("G8"), _
Key3:=Worksheets("wksJobCostInput").Range("H8")

I have tried including Worksheets("wksJobCostInput"). before Range,
and I have tried the following line of code:

Range("valJobCostEntryList").Select

which works just fine. I can't use select, however, because I am
capturing that Event to bring up a dialog box, and I don't want to
call the dialog box, just to sort the darned range!

Can anyone help me please?

 

[FSt1]

hi
your code works in my test but using my named range and my sheet name.
what is the range address of your named range? and what sheet is that named
range assigned to.

regards
FSt1

 

[Jim Cone]

Try changing the keys to...
Key1:=Range("valJobCostEntryList").Columns(1) 'whatever corresponds to column E
and so on for Key2 and Key3

Note: Spammers harvest email addresses from newsgroups, I'm told.
--
Jim Cone
Portland, Oregon USA
(e-mail address removed) (remove xxx)
(Free trial of Special Sort Excel add-in - two dozen ways to sort)



<[email protected]>
wrote in message
I am getting a "Subscript out of range" error trying to run a simple
bit of code. This is the code, essentially:

Range("valJobCostEntryList").Sort _
Key1:=Worksheets("wksJobCostInput").Range("E8"), _
Key2:=Worksheets("wksJobCostInput").Range("G8"), _
Key3:=Worksheets("wksJobCostInput").Range("H8")

I have tried including Worksheets("wksJobCostInput"). before Range,
and I have tried the following line of code:

Range("valJobCostEntryList").Select

which works just fine. I can't use select, however, because I am
capturing that Event to bring up a dialog box, and I don't want to
call the dialog box, just to sort the darned range!

Can anyone help me please?

 

[michael.blaustein]

Well, I figured it out. Thanks for your help. In the properties of
the worksheet, the (name) is "wksJobCostInput", but the name is "JC -
Tenant Alterations". It turns out when I do this:

Worksheets("wksJobCostInput").Range("valJobCostEntryList"), it can't
find the range, but when I do this:

Worksheets("JC - Tenant Alterations").Range("valJobCostEntryList")

it can find it no problem. That kind of sucks, because I plan on
changing the visible name at some point, and I will have to go into my
code and adjust all of the references. Does anyone know if this is a
known issue?

Michael

p.s. thanks for the heads up about spammers getting my e-mail
address. I am using google groups to post, and it doesn't seem to
allow me to change it. Well, I guess I'll just have to hope gmail
catches all of the spam.

 

[Per Jessen]

Hi

You can use the VBA level sheet names. Look in the project explorer window,
for each sheet you will see two sheet names:

Sheet1(MySheetName)

where Sheet1 is the VBA level sheet name and MySheetName is the visible
sheet name on the sheet tab, which you are changeing.

Using the first, you don't use the Worksheet(..) reference, just

Sheet1.Range("...")

Hopes this helps.

Regards,
Per


<[email protected]> skrev i meddelelsen
Well, I figured it out. Thanks for your help. In the properties of
the worksheet, the (name) is "wksJobCostInput", but the name is "JC -
Tenant Alterations". It turns out when I do this:

Worksheets("wksJobCostInput").Range("valJobCostEntryList"), it can't
find the range, but when I do this:

Worksheets("JC - Tenant Alterations").Range("valJobCostEntryList")

it can find it no problem. That kind of sucks, because I plan on
changing the visible name at some point, and I will have to go into my
code and adjust all of the references. Does anyone know if this is a
known issue?

Michael

p.s. thanks for the heads up about spammers getting my e-mail
address. I am using google groups to post, and it doesn't seem to
allow me to change it. Well, I guess I'll just have to hope gmail
catches all of the spam.