Solved: Sort Data in 1 column or the other

[YellowLabPro]

This is currently posted on MrExcel. I hate to cross-post, but I am stuck on this and have not had any responses.

The next to the last message is the current status and hopefully provides complete understandable details. If not please post w/ more questions and I will try to clarify.

http://www.mrexcel.com/board2/viewtopic.php?t=229808

Thanks,

YLP

[Bob Phillips]

[vba]

Sub Sort()
Dim ws1 As Worksheet
Dim LRow As Long
Dim strSortColumn As String

Set ws1 = Workbooks("TGS Item Record Creator.xls").Sheets("Record Creator")

LRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

strSortColumn = InputBox(Title:="Sort Item Records", _
Prompt:=("Enter Column Letter: (W) or (Y)" & vbNewLine & _
"Item# = (W)" & vbNewLine & _
"Item Description = (Y)"), _
Default:="Enter W or Y")

'strSortColumn = UCase(InputBox("Enter Column Name to Sort " & vbCrLf & "Enter only Column Label ""W"" or ""Y""" _
'& vbCrLf & "W= Item#" & vbCrLf & "Y= Item Description"))

If UCase(strSortColumn) <> "W" And UCase(strSortColumn) <> "Y" Then
MsgBox "Did You Not Want to Sort?", vbQuestion, "Not Sorted"
Exit Sub
End If

ws1.Range("A5:AH" & LRow).Sort Key1:=Range(strSortColumn & "21"), _
Order1:=xlAscending, _
Header:=xlGuess


Range("W5").Activate
End Sub
[/vba]

[YellowLabPro]

Thanks XLD!
I will comb through this to see what you did to make it work. One thing though-- what is the "21" role here?

[Bob Phillips]

It's the row number.

[YellowLabPro]

Thanks Xld,

Ok, but it seems to have no direct reference to the sort. Row 21 is an inconsequential reference. This is why some of these things confuse me.

I may have misunderstood... let me re-check my work.

[YellowLabPro]

Restated after further investigation:
I think I see how the program is working-- if you will confirm my findings.
The variable strSortColumn is storing the input of input box, in this case either a W or a Y for the specific Column.

Then this value is passed along to the sort statment
ws1.Range("A5:AH") & LRow).Sort Key1:=Range(strSotrColumn & "21")....

If this is correct then my question is why is the row in quotes and how come row 21 works? This particular case 21 is a quarter of the way down in the range. Is the row necessary so that the sort expands to cover the entire range?

Excuse my long windedness... I am eager to learn this


thanks for all your assistance.

YLP

[Bob Phillips]

Restated after further investigation:
I think I see how the program is working-- if you will confirm my findings.
The variable strSortColumn is storing the input of input box, in this case either a W or a Y for the specific Column.

Correct.

Then this value is passed along to the sort statment
ws1.Range("A5:AH") & LRow).Sort Key1:=Range(strSotrColumn & "21")....

Correct (spelling apart).

If this is correct then my question is why is the row in quotes and how come row 21 works? This particular case 21 is a quarter of the way down in the range. Is the row necessary so that the sort expands to cover the entire range?

It is in quotes becuase address is a string. In reality you don't need the quotes, & will concatenate to a string.

Why 21? No idea, it was in your code. I would think you can get away with any row number between 5 and LRow, because that is the liits of the data being sorted.

[YellowLabPro]

Thanks Xld,
Your assistance is greatly appreciated!

YLP