问题描述
我有一张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#.
推荐答案
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列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！