当附件只换成例二后,VBA代码更换为下面的代码(增加了TEXT),显示有错误,请大侠们帮助修改
Sub test()
Dim ar(), br()
i% = Range("A3").End(xlDown).Row
If Cells(i, 1) = "" Then Exit Sub
ar = Range("a3:c" & i).Value
ReDim br(1 To i * 2, 1 To 4)
For i = 3 To 1 Step -2
For i2% = 1 To UBound(ar)
If stemp$ = ar(i2, i) Then
br(r, 4) = br(r, 4) + 1
Else
r = r + 1
br(r, 1) = Cells(i2 + 2, i).Comment.Text
br(r, 2) = ar(i2, i)
br(r, 3) = Left(br(r, 1), 2)
br(r, 4) = 1
If r > 1 Then br(r - 1, 1) = br(r - 1, 1) & "-" & Left(br(r - 1, 1), 3) & Text((Right(br(r - 1, 1), 2) + br(r - 1, 4) - 1), "00")
stemp$ = ar(i2, i)
End If
Next
Next
If r > 1 Then
br(r, 1) = br(r, 1) & "-" & Left(br(r, 1), 3) & Text((Right(br(r, 1), 2) + br(r, 4) - 1), "00")
[a19].Resize(r, 4) = br
End If
End Sub
|