vba 自动生成序列号，根据优先级列自动排序行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12056036/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Autogenerate Serial Number with Rows Automatically Sorted According to Priority Column
提问by Dave
I require a macro to autogenerate a serial number on Column A as soon as information is entered in column B. Column C allows for prioritisation of the information in column B. I currently have a macro that sorts the rows according to prioritisation. Thus priorities 1 would be sorted at top of the sheet, followed by 2 etc. Thus the question how to autogenerate a serial number taking in consideration that the sort macro will move the rows around according to assigned priority.
我需要一个宏来在 B 列中输入信息后立即在 A 列上自动生成序列号。C 列允许对 B 列中的信息进行优先级排序。我目前有一个根据优先级对行进行排序的宏。因此，优先级 1 将在工作表的顶部排序，然后是 2 等。因此，考虑到排序宏将根据分配的优先级移动行，如何自动生成序列号的问题。
Serial number should be a simple numeric sequence starting at 1,2,3..... Not sure how query for the last number used in order to create next number.
序列号应该是一个简单的数字序列，从 1,2,3..... 不确定如何查询最后一个数字以创建下一个数字。
回答by Tim Williams
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, c As Range
Set rng = Application.Intersect(Target, Me.Columns(2))
If Not rng Is Nothing Then
'protect against processing (eg) the insertion of a whole column
If rng.Cells.Count < 100 Then
For Each c In rng.Cells
If Len(c.Value) > 0 And Len(c.Offset(0, -1).Value) = 0 Then
c.Offset(0, -1).Value = GetNextNumber()
End If
Next c
End If
End If
End Sub
Function GetNextNumber()
Dim rv
With Me.Range("H1")
rv = .Value
.Value = .Value + 1
End With
GetNextNumber = rv
End Function
回答by user3357963
I imagine it will be some variation of =MAX(A:A)+1
我想这会是一些变化 =MAX(A:A)+1
If you are using Excel 2003 you may want to restrict the range to say =MAX(A1:A65536)+1
如果您使用的是 Excel 2003，您可能需要限制范围说 =MAX(A1:A65536)+1
and in VBA
并在 VBA 中
Option Explicit
Sub test()
Dim lookuprng As Range
Dim nextSerial As Long
Set lookuprng = Sheet1.Range("A:A")
nextSerial = WorksheetFunction.Max(lookuprng) + 1
End Sub
