Sorting macro

[sspatriots]

Hi,

I've recorded a sorting macro (below). However, when I try and run it the cells that end with an "R#" move to the end of the sort. Is there a way to make this sort the table column on just the first 4 characters of the PO#? I know I can add a helper column and sort on that, but I'm afraid it may mess up another spreadsheet that references this table. Thanks, SS

Sample of my values and how they are sorting in ascending order:

5013
5014
5015
5016
5017
5018
5019
5020
0147 R1
0789 R2
1483 R1
1484 R2
1497 R2
1786 R1
1807 R1
1820 R1
2327 R1

Sub SortPONo()
'
' Sort PO Numbers Macro
'

'
    ActiveWorkbook.Worksheets("2022").ListObjects("Table46").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("2022").ListObjects("Table46").Sort.SortFields.Add2 _
        Key:=Range("Table46[PO'#]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("2022").ListObjects("Table46").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Alex Blakenburg]

Its a common issue for us that in simplying the example posted here, the data becomes unrepresentative of the real data.
We definitely need to know if the data is the result of a formula or is a constant and whether the data is text or numeric or a mix.

I am assuming you are not hard coding the PO number in the Hyperlink formula as indicated in your example.

Just on the off chance that you have in fact already converted everything to text, in your original code in post #1 try replacing your xlSortTextAsNumbers with xlSortNormal

 

[sspatriots]

Its a common issue for us that in simplying the example posted here, the data becomes unrepresentative of the real data.
We definitely need to know if the data is the result of a formula or is a constant and whether the data is text or numeric or a mix.

I am assuming you are not hard coding the PO number in the Hyperlink formula as indicated in your example.

Just on the off chance that you have in fact already converted everything to text, in your original code in post #1 try replacing your xlSortTextAsNumbers with xlSortNormal

Some of the data (hyperlinks) are the result of a formula linked to a button that allows the user to select the cell they want to hyperlink and it opens to a folder where they can select the file they are linking. Only issue is that it puts the link on the spreadsheet in the form of a formula like I had in one I put in post #9. I'm posting the code that creates that hyperlink below. Maybe someone can tell me how to create this link in this code such that it doesn 't leave a formula in the cell and just the text with the hyperlink.

Here is the code:

Sub HyperlinkFile()
Dim xGetFile As Object
Dim fName As String, sFullFilename As String, sFileName As String

'Opens dialog box to Pick File to Hyperlink

Set xGetFile = Application.FileDialog(msoFileDialogFilePicker)


        xGetFile.InitialFileName = "\\company.local\drives\Manufacturing\Commercial Archive\" 'Selected File full path


With xGetFile
    .Title = "Spreadsheet Creative - Select File to Hyperlink"
    If .Show = -1 Then
'Selected File full path
        fName = .SelectedItems(1)
        xGetFile.InitialFileName = "\\company.local\drives\Manufacturing\Commercial Archive\" 'Selected File full path

       
    Else
        fName = ""
    End If
End With

'Test if user pressed cancel
If fName = "" Then Exit Sub
'Resume code

'Get File name from path
sFullFilename = Right(fName, Len(fName) - InStrRev(fName, "\"))
On Error Resume Next

'Change display text to the Selected File name WITHOUT FILE EXTENSION
'if you want to display the file extension then change the formula from sFileName to sFullFilename
'sFileName = Left(sFullFilename, (InStr(sFullFilename, ".") - 1))

sFileName = ActiveCell.Text

On Error Resume Next

'Add HYPERLINK formula to active cell
ActiveCell.Formula = "=HYPERLINK(""" & fName & """,""" & sFileName & """)"

End Sub

 

[Alex Blakenburg]

Firstly, did you try your original code changing the sort to xlSortNormal ?

Secondly on the Hyperlink, it probably should be a separate question but just recording the macro will pretty much get you there.
Just replace your last ActiveCell.Formula line with this one:

ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, _
                          Address:=fName, TextToDisplay:=sFileName

 

[sspatriots]

Firstly, did you try your original code changing the sort to xlSortNormal ?

Secondly on the Hyperlink, it probably should be a separate question but just recording the macro will pretty much get you there.
Just replace your last ActiveCell.Formula line with this one:

ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, _
                          Address:=fName, TextToDisplay:=sFileName

I did try the xlSortNormal. That is where I actually started having the sort issues. It would put everything with the "R#" suffix at the bottom of my sort list and I need everything to sort on the first for numbers of the cells in that column and ignore the "R#"s.

Before I saw your last post above, I actually did try and record the steps, however, it gave me the actual file path when it was recorded. So I substituted it with "fName" and it worked. I removed everything else. Below is what worked for me. I'll will post back if everything is working after I make these few changes. Thank you again for all your help.

'Add HYPERLINK formula to active cell
'ActiveCell.Formula = "=HYPERLINK(""" & fName & """,""" & sFileName & """)"

'Add Anchored HYPERLINK to active cell
    With ws
    
     .Hyperlinks.Add Anchor:=Selection, _
     Address:=fName
     
    End With