编写一个宏，根据数字顺序将数据插入excel列 [英] Write a macro to insert data into excel column based on numerical order

问题描述

我有一张Excel表格，其中包含我希望锁定的数据列，并且只能通过另一张表格中包含的宏进行编辑。



我需要的数据更新如下所示：Ctrl＃的按数字顺序组织

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

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.

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

示例：

如果我将 002 Desc2 添加到控制表中那：

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

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

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

任何帮助将不胜感激。



我尝试过：



我尝试录制宏，但无法使用数字顺序调整网格。录制的宏在指定的单元格中插入新数据，而不是基于Ctrl＃。

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#.

推荐答案

嗯...



我讨厌提供现成的解决方案，但是今晚我要做个例子;）

Well...

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

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!

这篇关于编写一个宏，根据数字顺序将数据插入excel列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！