怎樣利用Excel進行VB編程

Const?C2?=?15?'非連續數調整值

Const?S?=?10?'設定連續範圍

Sub?process()

Dim?a,?i!,?j!,?d!,?n

Range("A1").Sort?key1:=Range("A1"),?order1:=xlAscending

a?=?WorksheetFunction.Transpose(Range("A1:A"?&?[A65536].End(xlUp).Row))

For?i?=?1?To?UBound(a)

d?=?a(i)?+?S

For?j?=?i?To?UBound(a)

If?j?+?1?>?UBound(a)?Then?Exit?For

If?a(j?+?1)?>?d?Then?Exit?For

Next

If?i?=?j?Then?'非連續數

a(i)?=?a(i)?-?C2

Else?'連續數

n?=?a(i)?-?C1

For?k?=?i?To?j

a(k)?=?n

Next

i?=?j

End?If

Next

Range("B1").Resize(UBound(a))?=?WorksheetFunction.Transpose(a)

End?Sub

效果如圖，可下載附件參考