|
对象所在单元格(可能是合并单元格)录入连续的数字
谢谢大侠了
本帖最后由 hwc2ycy 于 2013-5-26 15:32 编辑
字典法,效率高。 - Sub 入口()
- Dim objRg As Range
- Dim rg As Range
- Dim lCount As Long
- On Error Resume Next
- '强制选择单元格区域
- Do
- Set objRg = Application.InputBox(prompt:="请选择要编号的对象所在的单元格区域", Title:="给指定单元格区域内的对象重新编号", Type:=8)
- Err.Clear
- Loop Until Not objRg Is Nothing
- On Error GoTo ErrorHandler
- '关闭屏幕更新,警告,事件
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Application.EnableEvents = False
- '字典
- Dim objDic As Object
- Set objDic = CreateObject("scripting.dictionary")
- '添加地址
- Dim objShape As Shape
- For Each objShape In ActiveSheet.Shapes
- objDic(objShape.TopLeftCell.Address(False, False)) = ""
- Next
- '找对像
- For Each rg In objRg
- If objDic.exists(rg.Address(False, False)) Then
- lCount = lCount + 1
- rg.Value = lCount
- End If
- Next
- '信息提示
- If lCount > 0 Then
- MsgBox "一共找到了 " & lCount & " 个对象"
- Else
- MsgBox objRg.Address(False, False) & "区域内无对象"
- End If
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- Application.EnableEvents = True
- Set objDic = Nothing
- Exit Sub
- ErrorHandler:
- MsgBox Err.Number & vbCrLf & _
- Err.Description
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- Application.EnableEvents = True
- End Sub
复制代码
|
|