Write a macro to insert data into excel column based on numerical order

Question

0.00/5 (No votes)

See more:

I have an Excel sheet with columns of data that I would like to keep locked and only editable by Macro contained in another sheet.

The data I need to update looks like this: The Ctrl#'s are organized in numerical order

C#

Ctrl#    Note	Ctrl#	Note	Ctrl#	Note
001	     Desc1  009	    Desc9	019	    Desc19
003	     Desc3  010	    Desc10	020     Desc20
004	     Desc4  013	    Desc13	021	    Desc21

I want to use a 2nd sheet with a two columns for Data Entry like shown below and when a new row is added, update the first sheet and adjust the grid accordingly.

C#

001	Desc1
003	Desc3
004	Desc4
009	Desc9
010	Desc10
013	Desc13
019	Desc19
020	Desc20
021	Desc21

Example:
If I add 002 Desc2 to the control sheet such that:

C#

001	Desc1
002 Desc2
003	Desc3
004	Desc4
009	Desc9
010	Desc10
013	Desc13
019	Desc19
020	Desc20
021	Desc21

....I want the grid to adjust like so

C#

Ctrl#    Note	Ctrl#	Note	Ctrl#	Note
001	     Desc1  004	    Desc4	013	    Desc13
002	     Desc2  009	    Desc9	019     Desc19
003	     Desc3  010	    Desc10	020	    Desc20
                                021	    Desc21

Any help would be appreciated.

What I have tried:

I tried recording a macro, but can't get the grid to adjust using the numerical order. The recorded macro inserts the new data in the specified cell, not based on the Ctrl#.

Posted 17-Aug-16 10:15am

AdvancedDNA

Updated 17-Aug-16 11:41am

Add a Solution

Comments

Maciej Los 17-Aug-16 16:46pm

What have you tried? Where are you stuck?
There's no chance to order your data into numerical order till the Ctr#'s contain leading zeros...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2016-08-17T11:41:00

Well...

I hate to provide ready-to-use solution, but tonight i'm gonna to make an exception ;)

VB

Option Explicit

Sub SortAndExportData()
    Dim wbk As Workbook
    Dim srcwsh As Worksheet, dstwsh As Worksheet
    Dim rangeToSort As Range
    Dim i As Integer, r As Integer, c As Integer, divider As Integer
    
    'define workbook
    Set wbk = ThisWorkbook
    'define source worksheet
    Set srcwsh = wbk.Worksheets("Sheet2")
    'define range to sort
    Set rangeToSort = srcwsh.UsedRange  'or: srcwsh.Range("A1:B11")
    'sort data, threat text as numbers
    With srcwsh.Sort
        .SortFields.Clear
        'define Ctrl# header as a Key!
        .SortFields.Add Key:=rangeToSort.Cells(1, 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        .SetRange rangeToSort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    'define destination worksheet
    Set dstwsh = wbk.Worksheets("Sheet1")
    dstwsh.UsedRange.Delete Shift:=xlShiftUp
    'define number of rows for each column
    divider = 3
    r = 2
    c = 0
    For i = 2 To rangeToSort.Rows.Count
        'add headers
        dstwsh.Range("A1").Offset(ColumnOffset:=c) = "Ctrl#"
        dstwsh.Range("B1").Offset(ColumnOffset:=c) = "Note"
        'values
        dstwsh.Range("A" & r).Offset(ColumnOffset:=c) = rangeToSort(i, 1)
        dstwsh.Range("B" & r).Offset(ColumnOffset:=c) = rangeToSort(i, 2)
        r = r + 1
        If CInt(i - 1) Mod divider = 0 Then
            r = 2
            c = c + 2
        End If
    Next

Exit_SortAndExportData:
    On Error Resume Next
    Set wbk = Nothing
    Set dstwsh = Nothing
    Set srcwsh = Nothing
    Set rangeToSort = Nothing

End Sub

Feel free to change it to your needs!