excel - 试图在vba中对数字进行分组

Question

我正在尝试从 E 列中对一个以 1 开头的数字进行分组，结果应如下所示:

Column
E   I
1   1-52
.   54-56
.   58-59
.
52
54
55
56
58
59

我开始这样写:

Sub Group_Numbers()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  
Range("I1") = Range("E1")
k = 1
  a = Range("E1", Range("E" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 2 To UBound(a)
  If a(i, 1) <> Val(a(i - 1, 1)) + 1 Then
    k = k + 1
    b(k, 1) = a(i, 1)
    Else
        b(k, 1) = Split(b(k, 1), "-")(0) & -a(i, 1)
    End If
  Next i
    Range("I2").Resize(l).Value = b
End Sub

但是，它会提示错误 9 subscript out of range。希望能在这里得到帮助。
非常感谢!

Answer 1

我会做以下事情

Option Explicit

Public Sub Example()
    Dim ws As Worksheet  ' define worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
    
    Dim Data() As Variant  ' read input data into array
    Data = ws.Range("E1", "E" & LastRow).Value2
    
    Dim OutData() As Variant  ' define output array
    ReDim OutData(1 To UBound(Data, 1), 1 To 1) As Variant
    Dim iOut As Long
    iOut = 1
    
    Dim StartVal As Long
    StartVal = Data(1, 1)  ' initialize start value of a group
    
    Dim iRow As Long
    For iRow = 2 To UBound(Data, 1) ' loop through values
        ' check if value is previous value +1
        If Data(iRow, 1) <> Data(iRow - 1, 1) + 1 Then
            ' if not write output from StartVal to previos value
            OutData(iOut, 1) = StartVal & "-" & Data(iRow - 1, 1)
            iOut = iOut + 1
            ' and set curent value as new group start
            StartVal = Data(iRow, 1)
        End If
    Next iRow
    
    ' close last group
    OutData(iOut, 1) = StartVal & "-" & Data(iRow - 1, 1)
    
    ' write array back to cells
    ws.Range("I1").Resize(RowSize:=iOut).NumberFormat = "@"  'format cells as text so `1-2` does not get converted into date.
    ws.Range("I1").Resize(RowSize:=iOut).Value2 = OutData
End Sub