|
楼主 |
发表于 2019-3-17 15:26
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
谢谢蓝版,运行提示下标越界
- Sub lqxs()
- Dim d, arr, i&, mypath$, myname$, j&, aa, bb
- Set d = CreateObject("scripting.dictionary")
- Application.ScreenUpdating = False
- Sheet1.Activate
- arr = [aj1:at299]
- For j = 1 To UBound(arr, 2) Step 6
- For i = 1 To UBound(arr)
- If arr(i, j) <> "" Then
- aa = Split(arr(i, j), ";")
- d(Trim(aa(1))) = arr(i, j + 1) & "," & arr(i, j + 2) & "," & arr(i, j + 3) & "," & arr(i, j + 4)
- End If
- Next
- Next
- mypath = ThisWorkbook.Path & ""
- myname = Dir(mypath & "*.xls")
- Do While myname <> ""
- If InStr(myname, "居委会") Then
- With GetObject(mypath & myname)
- arr = .Sheets("中继段").UsedRange
- With .Sheets("中继段")
- For i = 1 To UBound(arr)
- If InStr(arr(i, 1), "光纤标号") Then
- aa = Split(arr(i, 1), ":")
- If d.exists(Trim(aa(1))) Then
- bb = Split(d(Trim(aa(1))), ",")
- .Cells(i + 16, 8) = bb(0): .Cells(i + 16, 17) = bb(1)
- .Cells(i + 20, 8) = bb(2): .Cells(i + 20, 9) = bb(3)
- End If
- End If
- Next
- End With
- .Close True
- End With
- End If
- myname = Dir
- Loop
- MsgBox "ok"
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|