|
本帖最后由 micch 于 2019-1-23 13:29 编辑
哦,那不如干脆分开写两个match,不过还是用别的办法吧,我没有textjoin函数,写出筛选公式,也合并不了;
写个代码吧
- Sub test()
- Set d = CreateObject("scripting.dictionary")
- Dim arr, brr(99, 2), i%, n%, x$
- For Each sh In Worksheets
- x = sh.Name
- If Left(x, 2) = "差旅" Then
- arr = Worksheets(x).[d8:g11]: d("") = ""
- For i = 1 To 4
- d(arr(i, 1)) = "": d(arr(i, 4)) = ""
- Next
- brr(n, 0) = x
- brr(n, 1) = Mid(Join(d.keys, "、"), 2)
- brr(n, 2) = d.Count - 1
- d.RemoveAll: n = n + 1
- End If
- Next
- With Worksheets("出差城市")
- .Cells.Clear
- .[a1:c1] = Array("", "出差城市", "城市数量")
- .[a2].Resize(n, 3) = brr
- With .UsedRange
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- .EntireColumn.AutoFit
- .RowHeight = 22
- End With
- End With
- End Sub
复制代码
|
|