|
cyb412204170 发表于 2012-7-9 12:48
你好,结帐单号07-03-008中同一房号有两个服务员,如何才能全部显示?
也就是以后同一房号超过一个服务员 ...
试试这个
- Sub 房号汇总()
- Dim Ar1, Arr11(), i%, D, M
- Dim SD As Date
- Set D = CreateObject("scripting.dictionary")
- SD = Range("b1")
- Ar1 = Sheets("记录表").[a1].CurrentRegion
- For i = 2 To UBound(Ar1)
- If Ar1(i, 1) = SD Then
- If D.exists(Ar1(i, 2)) Then
- Arr11(2, D(Ar1(i, 2))) = Arr11(2, D(Ar1(i, 2))) + Ar1(i, 6)
- If Ar1(i, 4) <> "" Then
- If Arr11(3, D(Ar1(i, 2))) = "" Then
- Arr11(3, D(Ar1(i, 2))) = Ar1(i, 4)
- Else
- Arr11(3, D(Ar1(i, 2))) = Arr11(3, D(Ar1(i, 2))) & "," & Ar1(i, 4)
- End If
- End If
- Else
- M = M + 1
- ReDim Preserve Arr11(1 To 3, 1 To M) '这句原来的2改成3
- D(Ar1(i, 2)) = M
- Arr11(1, M) = Ar1(i, 2)
- Arr11(2, M) = Ar1(i, 6)
- If Ar1(i, 4) <> "" Then Arr11(3, M) = Ar1(i, 4) '加这句
- End If
- End If
- Next
- Range("a3:c65536").ClearContents 'b改成c
- [a3].Resize(UBound(Arr11, 2), UBound(Arr11)) = Application.Transpose(Arr11)
- End Sub
复制代码
|
|