|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 ykcbf1100 于 2024-12-13 15:40 编辑
数据不规范,所以,在转换以前还得先清洗数据。- Sub ykcbf() '//2024.12.13
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- With Sheets("填空")
- r = .UsedRange.Find("*", , -4163, , 1, 2).Row
- arr = .[a1].Resize(r, 1)
- End With
- a = [{"A.","B.","C.","D.","E."}]
- b = [{2,3,4,5,6}]
- For x = 1 To UBound(a)
- d(a(x)) = b(x)
- Next
- ReDim brr(1 To r, 1 To 6)
- For i = 1 To UBound(arr)
- If Val(arr(i, 1)) Then k = k + 1: d1(k) = i
- Next
- On Error Resume Next
- For k = 1 To d1.Count
- r1 = d1(k)
- n = 1
- If k = d1.Count Then r2 = r Else r2 = d1(k + 1) - 1
- l = r2 - r1 + 1
- stt = Replace(Replace(arr(r1, 1), ".", "."), "、", ".")
- Z = InStr(stt, a(1))
- m = m + 1
- If Z Then
- brr(m, 1) = Left(stt, Z - 1)
- st = Split(WorksheetFunction.Trim(Mid(stt, Z)))
- For Each ma In st
- For x = 1 To UBound(a)
- If InStr(ma, a(x)) Then
- brr(m, d(a(x))) = ma
- Exit For
- End If
- Next
- Next
- Else
- brr(m, 1) = arr(r1, 1)
- End If
- For i = r1 + 1 To r2
- stt = Replace(Replace(arr(i, 1), ".", "."), "、", ".")
- st = Split(WorksheetFunction.Trim(stt))
- For Each ma In st
- For x = 1 To UBound(a)
- If InStr(ma, a(x)) Then
- brr(m, d(a(x))) = ma
- Exit For
- End If
- Next
- Next
- Next
- Next
- With Sheets("效果")
- .UsedRange.ClearContents
- .[a1].Resize(m, 6) = brr
- End With
- MsgBox "OK!"
- End Sub
复制代码
|
|