|
楼主 |
发表于 2014-10-8 11:50
|
显示全部楼层
QLZ0602 发表于 2014-10-6 11:32
是不是这样?
Sub demo()
Dim rng1 As Range, rng2 As Range, nr As Long, nc As Long
Dim i, j, k, n
Dim arr, brr, crr, drr
On Error Resume Next
Set rng1 = Application.InputBox("请选择需要提取的药品列", "选择", , , , , , 8)
If rng1 Is Nothing Then Exit Sub
On Error Resume Next
Set rng2 = Application.InputBox("请选择全部药品列", "选择", , , , , , 8)
If rng2 Is Nothing Then Exit Sub
With Sheets("全部药品")
nr = .Cells(Rows.Count, 1).End(xlUp).Row
nc = .Cells(1, Columns.Count).End(xlToLeft).Column
arr = .Range(.Cells(1, 1), .Cells(nr, nc))
brr = rng1.Value
crr = rng2.Value
ReDim drr(1 To UBound(arr), 1 To UBound(arr, 2))
For i = 2 To UBound(arr)
For j = 1 To UBound(crr)
If brr(j, 1) = "" Then Exit For
If brr(j, 1) = crr(i, 1) Then
n = n + 1
For k = 1 To UBound(arr, 2)
drr(n, k) = arr(i, k)
Next
Exit For
End If
Next
Next
For k = 1 To UBound(arr, 2)
drr(1, k) = arr(1, k)
Next
End With
With Sheets("模拟")
.Cells.Clear
.Range("a1").Resize(n, UBound(drr, 2)) = drr
End With
End Sub
老师你好,这个可以,但是提取时,没有带格式提取
请老师帮帮我修改下,可以带格式提取,好吧,谢谢老师了!
|
|