快速瀏覽
往期合集：【
2023年3月】【
2023年4月】【
2023年5月】【
2023年6月】【
2023年7月】【
2023年8月】【
2023年9月】【
2023年10月】
實用案例
|日期控件||
簡單的收發(fā)存||收費(fèi)管理系(Access改進(jìn)版)|
|電子發(fā)票管理助手||電子發(fā)票登記系統(tǒng)(Access版)||文件合并||表格拆分||審計憑證抽查底稿||
中醫(yī)診所收費(fèi)系統(tǒng)(Excel版)||
中醫(yī)診所收費(fèi)系統(tǒng)(Access版)||
銀行對賬單自動勾對|
收費(fèi)使用項目
|
財務(wù)管理系統(tǒng)||
工資薪金和年終獎個稅籌劃|
內(nèi)容提要
SheetChange事件
序號自動更新
大家好，我是冷水泡茶，今天在網(wǎng)上看到一個提問：
[求助] 插入行后序號能否自動更新？
需求：可能存在插入行的情況，如圖，插入行6。當(dāng)B6輸入內(nèi)容后，A6自動編號為5，但A7-A10的序號還是原來的序號5-8，能否自動更新成6-9？（A列序號可能會達(dá)上千個）
關(guān)于序號，我們可以采用很多種方法來生成，我也分享過【
EXCEL 小技巧/七種輸入序號的方法，總有一款適合你】，方法雖多，但是總有那么一點(diǎn)不完美，有時候需要重設(shè)公式。
借今天這個案例，我們來研究一下如何利用VBA來實現(xiàn)自動更新序號，不需要人工干預(yù)：
一、基本思路：
1、利用工作表的Change事件，當(dāng)B列單元格有變化時（包括單元格值的改變、刪除插入單元格、刪除插入行等），重寫序號。
2、代碼還是寫在Thisworkbook里，提高代碼的安全性，防止刪除工作表后代碼也被一起刪除。
3、當(dāng)Change事件發(fā)生時，我們把目標(biāo)工作表的A列存入數(shù)組，通過循環(huán)，把連續(xù)的自然數(shù)賦值給數(shù)組的元素，即重新順序編號。
4、把序號回寫到目標(biāo)工作表。
二、VBA代碼：
在Thisworkbook里：
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim shNames As String '把shNames的值改為你需要自動更新序號的工作表名，"All"表示所有工作表 shNames = "/Sheet1/Sheet2/" shNames = "/All/" If InStr(shNames, "/" & Sh.Name & "/") Or InStr(shNames, "/All/") Then If InStr(Sh.Cells(1, 1), "號") Then Call setSerialNumber(Target) End If End IfEnd Sub
Private Sub setSerialNumber(Target As Range) Dim arr(), lastRow As Long Dim ws As Worksheet Set ws = ActiveSheet With ws If Not Intersect(Target, .Cells(Target.Row, 2)) Is Nothing And Target.Row > 1 Then lastRow = .UsedRange.Rows.Count If lastRow = 1 Then .Cells(2, 1) = "" Exit Sub End If arr = .Range(.Cells(1, 1), .Cells(lastRow, 1)).Value For i = 1 To UBound(arr) - 1 arr(i + 1, 1) = i Next For i = UBound(arr) To 2 Step -1 If .Cells(i, 2) <> "" Then Exit For Else arr(i, 1) = "" End If Next .Cells(1, 1).Resize(UBound(arr), 1) = arr End If End WithEnd Sub代碼解析：
1、Line1~11，SheetChange事件，用來指定哪些表需要自動更新序號。這里加了一個判斷條件，就是目標(biāo)工作表的A1單元格需要包含“號”字，才執(zhí)行自動更新序號過程
2、Line13~38，setSerialNumber設(shè)置序號過程。
（1）Line18，判斷發(fā)生Change的單元格，行號大于2，并且包括B列單元格。
（2）Line20~23，如果最大數(shù)據(jù)行是1，表示除了第一行標(biāo)題，沒有其他內(nèi)容，清除A2單元格的序號，退出過程。
（3）Line24，把A列數(shù)據(jù)區(qū)域（也就是當(dāng)前序號）裝入數(shù)組。
（4）Line25~27，重新順序?qū)懭胄蛱?
（5）Line28~34，清除數(shù)據(jù)區(qū)域尾部空白行的序號。
（6）Line35，把序號寫入工作表。
注意，我們的數(shù)組是包括“序號”標(biāo)題的。開始我是從第2行開始把序號裝入數(shù)組的，但是會出現(xiàn)數(shù)據(jù)區(qū)域只有一個單元格的情況，而單獨(dú)一個單元格是不能直接賦值給數(shù)組的，以下語句會報錯：
arr = .Range(.Cells(1, 1), .Cells(lastRow, 1)).Value把表頭包括進(jìn)來可以避免這種情況。
好，今天就到這，我們下期再會。~~~~~~End~~~~~~
安利小店
安利的牙膏非常不錯，用了以后就不想再用其他的了；洗潔精、洗衣液也是日常必備，用過都說好！
合谷醫(yī)療
合谷醫(yī)療專攻各種疑難雜癥，尤其擅長腰頸椎疾病、兒童神經(jīng)發(fā)育異常、多動癥、自閉孤獨(dú)癥治療，可謂神乎其技！體驗過的直呼早點(diǎn)來就好了！
喜歡就點(diǎn)個贊、點(diǎn)在看、留言評論、分享一下唄！感謝支持！
