|
本帖最后由 要用就学 于 2014-5-11 11:42 编辑
Sub chaxun()
Dim i%, j%, y%
ends = Columns(1).Find("*", searchdirection:=xlPrevious).Row
Sheets("sheet2").Range("b2:f100").Clear
For Each Rng In Range("a2:a" & ends)
m = m + 1
If Rng Like Sheets("sheet2").Range("a2") Then
k = k + 1
Range("a" & m + 1).Copy Sheets("sheet2").Range("b" & k + 1)
Range("b" & m + 1).Copy Sheets("sheet2").Range("c" & k + 1)
Range("c" & m + 1).Copy Sheets("sheet2").Range("d" & k + 1)
End If
Next
i = Sheet2.Range("b1").CurrentRegion.Rows.Count - 1
j = Sheet2.Range("b1").CurrentRegion.Columns.Count
y = Application.CountA(Sheet3.Columns(1))
Sheet2.Range("b2").Resize(i, j).Copy Sheet3.Range("a1").Offset(y)
End Sub1、想把红色a2改成变量,使按照sheet2的A列依次查找并保存
2、蓝色部分是将每次找到的结果保存在sheet3表中,如果保存过就不保存怎么改进
- Sub Macro1()
- Dim arr, brr, crr, d, i&, s&, j%, k%
- Set d = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("a1").CurrentRegion
- brr = Range("a2").CurrentRegion
- ReDim crr(1 To UBound(arr), 1 To UBound(arr, 2))
- For i = 2 To UBound(arr)
- d(arr(i, 1)) = d(arr(i, 1)) & "," & i
- Next
- For i = 1 To UBound(brr)
- If d.exists(brr(i, 1)) Then
- x = Split(d(brr(i, 1)), ",")
- For j = 1 To UBound(x)
- s = s + 1
- For k = 1 To UBound(arr, 2)
- crr(s, k) = arr(x(j), k)
- Next
- Next
- End If
- Next
- Sheet3.Range("a1").Resize(s, UBound(crr, 2)) = crr
- End Sub
复制代码
|
|