sort numbers with letters

[rjplante]

I have the macro below which is used to sort a block of numbers and some numbers with letters:

306
306A
306D
308

The request has now been made to have a preceding letter. I need to adjust the macro so that it sorts the numbers in the following order. The preceding number will always be an N.

306
306A
306D
N306
308

I do not know how to adjust this macro as I obtained the macro here from one of the wizards who know VBA way better than I do. I don't fully understand how it works. If anyone can help me adjsut this so that it sorts the numbers like I have listed above, that would be great.

Thanks for the help.

Option Explicit

Dim numbers As New Collection

Sub SortNumbersWithLetters()

  Dim C As Range
  Dim a As Variant, n As Variant, m As Variant, x As Variant
  Dim i As Long, j As Long, k As Long
  Dim col As Long
 
  Set numbers = Nothing
  col = Sheets("Block Tracking All").Range("CF1").Value + 1
 
  a = Range("A5", Cells(Range("A" & Rows.Count).End(3).Row, col)).Value
  ReDim b(1 To UBound(a), 1 To col)
  For i = 1 To UBound(a)
    n = Val(a(i, 1))
    m = Mid(a(i, 1), Len(n) + 1)
    addnum Format(n, "000000000") & m
  Next
 
  i = 1
  For Each x In numbers
    n = Val(x)
    m = Mid(x, 10)
    b(i, 1) = Val(n) & m
    For j = 1 To UBound(a)
      If "" & a(j, 1) = b(i, 1) Then
        For k = 2 To UBound(a, 2)
          b(i, k) = a(j, k)
        Next
        Exit For
      End If
    Next
    i = i + 1
  Next
  Range("A5").Resize(UBound(b, 1), UBound(b, 2)).Value = b
 
  Call FC_CLEAR
 
End Sub

Sub addnum(C)

  Dim ii As Long
  For ii = 1 To numbers.Count
    Select Case StrComp(numbers(ii), C, vbTextCompare)
    Case 0, 1
      numbers.Add C, Before:=ii
      Exit Sub
    End Select
  Next
  numbers.Add C 'add to end
End Sub

 

[Akuini]

Sorry, I forgot to remove this line:
Range("i1:n13").Copy Range("A1")
Use this one instead:

Sub rjplante_2()
'rjplante_1 - sort numbers with letters
Dim c As Range
Dim va, vb
Dim x As String
Dim HC As String
Dim i As Long, m As Long, n As Long

HC = "G"  'temporary helper column, change to suit, must be a blank column on the right of data set

Set c = Range("A2", Cells(Rows.Count, "A").End(xlUp)) 'data start at A2, change to suit
vb = c
ReDim va(1 To UBound(vb, 1), 1 To 1)

For i = 1 To UBound(vb, 1)
    x = vb(i, 1)
    If IsNumeric(x) Then
        x = x & "#"
        n = 8 - Len(x)
        vb(i, 1) = String(n, "0") & x
        va(i, 1) = n
    Else
        If Left(x, 1) = "N" Then
            x = Mid(x, 2, 100) & "ZZ"
            n = 9 - Len(x)
            vb(i, 1) = String(n, "0") & x
            va(i, 1) = n
        Else
            n = 8 - Len(x)
            vb(i, 1) = String(n, "0") & x
            va(i, 1) = n
        End If
    End If
Next

Application.ScreenUpdating = False
c = vb
m = Columns(HC).Column - c.Column + 1
c.Offset(, m - 1) = va

With c
    .Resize(, m).Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
    DoEvents
    vb = .Value
    va = c.Offset(, m - 1).Value
    For i = 1 To UBound(vb, 1)
        vb(i, 1) = Mid(vb(i, 1), va(i, 1) + 1, 100)
        vb(i, 1) = Replace(vb(i, 1), "#", "")
        If InStr(vb(i, 1), "ZZ") Then vb(i, 1) = "N" & Replace(vb(i, 1), "ZZ", "")
    Next
    .Value = vb
End With

Columns(HC).ClearContents
Application.ScreenUpdating = True

End Sub

 

[rjplante]

My sheet has headers, sheet labels, formulas, and other macro buttons in rows 1 through row 4. When your updated macro button ran, all that data was deleted. I think it may have to do with the following line:

Range("i1:n13").Copy Range("A1")

This may be the start of it. I have modified the line to start at A5, the beginning of where my range is. but I do not know why it wipes out all the data in rest of the cells. I closed without saving and I am back in business, but I want to figure this out so that I can keep moving forward.

I am excited at the progress we have made together so far and I really appreciate the assistance.

 

[MARK858]

I think it may have to do with the following line:

Range("i1:n13").Copy Range("A1")

Are you using the code in post 20 or post 21?

 

[rjplante]

I think we got it squared away. I have been doing some testing and everything seems to be working awesome. Thank you so very much for all of your help.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.