Sort Issue

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Sort Issue

Post by adeel1 »

Hi All
In Sheet1 run code it will produce sheet Series and work as per my need

Now if you copy and paste code vertohori() exists in sheet1 to series sheet and run it
It will also run and there will be no error

Now issue is
In this state if you run again sheet1 code it will produce error (Previously it wasn't producing error)

Which I dig out is below line in vertohori() code (i modify this code and add some line in it)

If you comment it then codes work well if you delete series sheet and do above all

Code: Select all

xxx.Range("g1").Resize(Application.Max(Columns(V_V)) + 1, col).Sort Key1:=Range("g1"), Order1:=xlDescending, _
                     Orientation:=xlLeftToRight

i tried lot to resolve now i give up.


some more finding are

if you delete Series sheet and do F8 below line will not sort anything in series sheet

Code: Select all

wsh2.Range("A2:N" & n).Sort Key1:=wsh2.Range("A2"), Header:=xlNo
and after run vertohori() code this above line sorting in series sheet which producing error.

Note: Both Codes I am running from Ribbon some other file

Adeel
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 77998
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sort Issue

Post by HansV »

1) The code belongs in a standard module, not in a worksheet module. You don't have to copy it to each sheet.
2) Please indent your code consistently. It is extremely difficult to investigate code that has been indented randomly.
3) Please use Option Explicit at the top of each module and declare ALL variables explicitly.

The problem is your use of xlLeftToRight. Remove the Orientation:=xlLeftToRight part.

See the attached version. I cleaned up the code.

Sample.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Sort Issue

Post by adeel1 »

Hi Sir

Thanks for help and guiding me i will try to write code way you cleaned it up. :clapping:

how would we know which code will go in standard module and which one will in sheet module. :hairout:

now error is removed but result are incorrect

1> in sheet1 when you run code col B data will go to series sheet without any sorting, which is correct when run second code and run again first code it sort data in series sheet in col B.
2> after producing series sheet and run second code if i removed this line "Orientation:=xlLeftToRight" it is not sorting data in descending order
Capture.PNG
there is no issue in this code Worksheet_CreateList() as i am using this one since long only below line creating issue i just added this in second code.

Code: Select all

xxx.Range("G1").Resize(Application.Max(xxx.Columns(V_V)) + 1, col).Sort _
                            Key1:=xxx.Range("G1"), Order1:=xlDescending
Adeel
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 77998
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sort Issue

Post by HansV »

The only procedures that should go into a worksheet module are the worksheet's event procedures:
Private Sub Worksheet_Activate()
Private Sub Worksheet_Change(ByVal Target As Range)
etc.
Standard macros and functions should be stored in a standard module.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 77998
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sort Issue

Post by HansV »

Can you explain in simple terms what you want to sort in the second macro?
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Sort Issue

Post by adeel1 »

The only procedures that should go into a worksheet module are the worksheet's event procedures:
Private Sub Worksheet_Activate()
Private Sub Worksheet_Change(ByVal Target As Range)
etc.
Standard macros and functions should be stored in a standard module.
Thanks for this useful info. :chocciebar:
Can you explain in simple terms what you want to sort in the second macro?
when you run second code there msgbox will appear, vbyes will bring data of Col B and C and Vbno will bring data of Col D and E into G1 and onward
this data will go in same order of Col C or Col E horizontally G1 to onward
i am descending this order(highest to lowest) highest value come first in G1 and onward

if you run only second code in attached you can see this behavior with VByes and vbno.

in this state after hitting second code if you run first code issue occurs.

if delete below line whatever code i run first or two there is no error and code works well whiteout descending order.

Code: Select all

xxx.Range("G1").Resize(Application.Max(xxx.Columns(V_V)) + 1, col).Sort _
                            Key1:=xxx.Range("G1"), Order1:=xlDescending, Orientation:=xlLeftToRight
Adeel
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 77998
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sort Issue

Post by HansV »

I'm afraid I cannot help you with this, I don't understand the code. Sorry.
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Sort Issue

Post by adeel1 »

No issue sir, Thanks for you Help.

Adeel