|
- Private Sub CommandButton1_Click()
- Dim arr, brr, i&, j%, r&, k, crr(1 To 1, 1 To 8), temp
- 'Application.ScreenUpdating = False
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- With Sheets("Rawdata")
- temp = .FilterMode
- If temp Then .ShowAllData
- r = .Cells(Rows.Count, 1).End(xlUp).Row
- ' r = .Cells.Find("*", Cells(1, 1), xlValues, xlWhole, xlByRows, xlPrevious).Row '计算工作表最后一个非空行号
- arr = .Range("a2:j" & r)
- End With
- With Sheets("医院同期对比")
- brr = .[n5:u5]
- For i = 1 To UBound(brr, 2)
- d1(brr(1, i)) = i
- Next
- For i = 1 To UBound(arr)
- k = arr(i, 3) & "," & arr(i, 4) & "," & arr(i, 2) & "," & arr(i, 7)
- Debug.Print k
- If Not d.exists(k) Then Set d(k) = CreateObject("Scripting.Dictionary")
- If Not d(k).exists(arr(i, 9)) Then
- crr(1, d1(arr(i, 6))) = arr(i, 10)
- d(k)(arr(i, 9)) = crr
- Erase crr
- Else
- temp = d(k)(arr(i, 9))
- temp(1, d1(arr(i, 6))) = arr(i, 10)
- d(k)(arr(i, 9)) = temp
- End If
- Next i
- r = .Cells(Rows.Count, 2).End(xlUp).Row
- 'r = .Cells.Find("*", Cells(1, 1), xlValues, xlWhole, xlByRows, xlPrevious).Row
- brr = .Range("b1:i" & r)
- .[n6:u60000] = ""
- For i = 6 To UBound(brr)
- temp = .Cells(i, 2).MergeArea.Address
- temp = Split(temp, "$")(4)
- For j = i To temp
- k = brr(i, 1) & "," & brr(i, 2) & "," & brr(i, 3) & "," & brr(i, 4)
- If Not IsEmpty(d(k)) Then
- .Cells(j, "N").Resize(1, 8) = d(k)(brr(j, 7))
- End If
- Next j
- i = temp
- Next i
- End With
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|