VBA - simple sort is killing me!

zookeepertx

Active Member
Joined
May 27, 2011
Messages
480
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
 

zookeepertx

Active Member
Joined
May 27, 2011
Messages
480
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,430
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,430
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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).
 

zookeepertx

Active Member
Joined
May 27, 2011
Messages
480
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,430
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

zookeepertx

Active Member
Joined
May 27, 2011
Messages
480
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. ;)
 

zookeepertx

Active Member
Joined
May 27, 2011
Messages
480

ADVERTISEMENT

I'll try that right now
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,430
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
 

zookeepertx

Active Member
Joined
May 27, 2011
Messages
480
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,323
Messages
5,547,247
Members
410,781
Latest member
fabalshehhi
Top