VBA sorting creating a Range from Large Formula

Question

I am looking to sort a column alphabetically (E) but I need to change the values in my columns in order for the alphabetical order to be correct ex: I have R1, R2, R3...R100, CN1, CN10, etc... Alphabetically R100 goes before R2. So I have this very large formula with embedded Ifs, Concatenates, etc.. to add buffer zeros (R001, R002, R100, CN001,...)

Right now, my macro is adding a column with my formula (R1C1), sorting the table according to this new column, then deleting the column afterwards.

I am fairely new with VBA and wondering how I could create a range from this formula, and sort directly my correct column from this new range.

(how would I even create this range from my other range, wihtout storing it in another column?)

For exemple:

ActiveWorkbook.Worksheets("sheet1").Select

Columns("E:M").Sort key1:=CREATED_RANGE, order1:=xlAscending, Header:=slYes

My formula for the 000 padding is

=IF(ISBLANK(E2)=FALSE,IF(ISERROR(VALUE(RIGHT(E2,3)))=FALSE,E2,IF(AND(ISERROR(VALUE(MID(E2,2,1)))=TRUE,ISERROR(VALUE(RIGHT(E2,2)))=FALSE),CONCATENATE(LEFT(E2,2),0,RIGHT(E2,2)),IF((AND(ISERROR(VALUE(MID(E2,2,1)))=FALSE,ISERROR(VALUE(RIGHT(E2,2)))=FALSE)), CONCATENATE(LEFT(E2,1),0,RIGHT(E2,2)),IF(ISERROR(VALUE(MID(E2,2,1)))=TRUE,CONCATENATE(LEFT(E2,2),"00",RIGHT(E2,1)),CONCATENATE(LEFT(E2,1),"00",RIGHT(E2,1))))))," ")

Welcome to SO. I suggest you to post the formula as text, formatted as code, and not as image.please see How to create a Minimal, Complete, and Verifiable example — Foxfire And Burns And Burns

Error 1004 Error 1004 · Accepted Answer · 2018-10-22T07:26:46

@AriB try:

Option Explicit

Sub test()

Dim i As Long
Dim Lastrow As Long
Dim InputString As String

With Sheet1 '<= Let as assume that the data appears in Sheet1

    Lastrow = .Range("A" & Rows.Count).End(xlUp).Row '<= Let as assume that the data appears in Column A

    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove '<= Insert two columns for String & Number Part after column A
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    For i = 1 To Lastrow

        InputString = .Range("A" & i).Value

        If IsNumeric(Mid(InputString, 2, 1)) Then '<= check if only character 1 is letter
            .Range("A" & i).Offset(0, 2).Value = Mid(InputString, 2, (Len(InputString) - 1)) '<= Extract number from InputString and import it on third column
            .Range("A" & i).Offset(0, 1).Value = Mid(InputString, 1, 1) '<= Extract string from InputString and import it on third column
        ElseIf IsNumeric(Mid(InputString, 3, 1)) Then '<= check if only the first two characters are letters
            .Range("A" & i).Offset(0, 2).Value = Mid(InputString, 3, (Len(InputString) - 2))
            .Range("A" & i).Offset(0, 1).Value = Mid(InputString, 1, 2)
        ElseIf IsNumeric(Mid(InputString, 4, 1)) Then '<= check if only the first three characters are letters
            .Range("A" & i).Offset(0, 2).Value = Mid(InputString, 4, (Len(InputString) - 3))
            .Range("A" & i).Offset(0, 1).Value = Mid(InputString, 1, 3)
        End If
    Next i

    Range("A1:C" & Lastrow).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("B1:B" & Lastrow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal '<= sort with String
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("C1:C" & Lastrow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal '<= sort with Number
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:C" & Lastrow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Columns("B:C").Select '<= Select the two columns added to keep String & Number
    Selection.Delete Shift:=xlToLeft

End With

End Sub

Have in mind that you should change sheet, range or columns if your data dont appear in sheet1 column A.

VBA sorting creating a Range from Large Formula

1 Answers