VBA - simple sort is killing me!

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
567
Office Version
  1. 365
Platform
  1. Windows
Hello all!

This should be so easy, but not for me, apparently!
I have a table of data that can be of varying lengths and widths. My current macro does what I want - up to a point; that being to transpose the data and paste it in a new group underneath the current data. But then I want to sort it and that's where my problem comes in. The new info will always be pasted into column AE, starting in the third row below the raw data. The last row of raw data is represented by "lr"; the last column of raw data is represented by "LC". Once the 2nd table is created, the last row of that data is represented by "LR2" and the last column is the new value for "LC".

I've tried NUMEROUS versions of that part of my code and get various different errors each time. It's just a SORT; it should not be this hard!! I feel stupid!
Anyway, if someone can point out what I'm doing wrong, it would make me very happy, then I could move on to the next part of the macro.

Here is the code as I have it right now. I'll Bold/Underline where the error messages appear:
VBA Code:
Sub MacroPOBI()
'JennyDrumm 082820
' MacroPOBI Macro

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With

Cells.Select
With Selection
    .WrapText = False
    .MergeCells = False
    .Font.Name = "Times"
End With

Columns("AH:AH").Delete Shift:=xlLeft

lr = Cells(Rows.count, 31).End(xlUp).Row
LC = Cells(12, Columns.count).End(xlToLeft).Column

With Range(Cells(12, 31), Cells(lr, LC)).Select
    Selection.Copy
    Cells(lr + 2, 31).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End With

LC = Cells(lr + 2, Columns.count).End(xlToLeft).Column
LR2 = Cells(Rows.count, 31).End(xlUp).Row

[B][U]Range(lr + 2 & ":" & LC).Sort Key1:=Range("AF" & lr.Row + 2), Order:=xlAscending, Key2:= _
    Range("AG" & lr.Row + 2), Order:=xlAscending[/U][/B]

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
End With

End Sub


Here is what I have:

Capture1.JPG



Here is what I need:

Capture1b.JPG

Thank you for looking at this.

Jenny
 
RE: XL2BB - unfortunately, I'm at work and am not allowed to download or install anything on this computer. The IT dept would have a nervous breakdown ;)
you didn't supplied any range information for your data or the values of the variables, the numbers above do not make any difference if you have done your variables correctly. The code line would not run if I didn't put in some numbers.

If you have formulas or data to the right of AG then your LC won't be correct in the sort.

I apologize - by range information do you mean the column headers and row numbers? When I took my screenshots to post above, I made sure to include those in the images. I hoped that would work okay.

I used LC and LR2 because there's no telling how much raw data there's going to be in a given worksheet. The rows (In the raw data. In the final table, they've been transposed to columns) represent the styles and there could be any number of styles on a particular PO. Similarly, on the raw data, the column headers (transposed on the result to become rows) are store designations and there could be any number of stores on a PO. Could be 1, could be 12, or any other quantity. I thought using LC and LR2 would cover the irregularity of that facet of the project, since I never know how large or small the area to be sorted will be.

I'm sorry if I'm being unclear; I really am trying to get this across to you, but I stink at that. (Often, I use too many words)

Jenny
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I'm sorry if I'm being unclear; I really am trying to get this across to you, but I stink at that. (Often, I use too many words)
Not to worry about that, we'll get there ;) If you can't use XL2BB can you upload the data to a free file sharing site?
I'll ask any specific questions once I have the sample.
 
Upvote 0
The other thing that you need to test is that
Range("AF" & lr + 2) and Range("AG" & lr + 2) do both have values in the cells (or +3 that you appear to be using in your last code).
 
Upvote 0
The other thing that you need to test is that
Range("AF" & lr + 2) and Range("AG" & lr + 2) do both have values in the cells

Oooh, they do not! I'm going to have them put in within the macro, but haven't done it yet! In the example I've been playing with, there's nothing there. Hang on, let me put something in there and see what happens
 
Upvote 0
Oooh, they do not! I'm going to have them put in within the macro, but haven't done it yet! In the example I've been playing with, there's nothing there. Hang on, let me put something in there and see what happens
Test that with the code that had the 1004 error not the later code ;)
 
Upvote 0
Darn! I got up with the same "reference number is not valid" error AND ended up with the "infinite circle of death" so had to break out of the macro. (I always kind of wonder if you didn't break out of that, would it just keep going forever? Somebody has to wonder about things like this. ;)
 
Upvote 0
Sorry test with
Rich (BB code):
Range(Cells(lr + 2, "AF"), Cells(LR2, LC)).Sort Key1:=Range("AF" & lr + 2), Order1:=xlAscending, Key2:= _
   Range("AG" & lr + 2), Order2:=xlAscending
 
Upvote 0
Oooh, oooh, OOOH! SO close!! It's sorting correctly, but one of the rows got thrown to the bottom of the data , and some of the things got separated a little bit. But we're ALMOST THERE!

In the example below, column AF has 1002/NP, then 7079, then 1002. When they're transposed into a row, they need to stay together, left to right - 1002/NP, 7079, 1002. Same with 1006/AT, 7099, 1006. Transposed to a row they should be 1006/AT, 7099, 1006, etc. ( I only added the coloring to make things easier to see; it has no REAL purpose, LOL!)

Anyway, I hope this helps; we're SO close now!

It's after 5:00 here, so they're going to chase us out of the building soon, but I'll be back tomorrow. Thank you for your patience!

Very close.JPG
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top