|
楼主 |
发表于 2014-1-10 17:13
|
显示全部楼层
Sub test()
Dim y As Integer, T As Integer, W As Integer, x As Integer, h As Integer, Mr As Integer, MySearch1 As Range, MySearch2 As Range, MySR1 As Integer, Firstaddr1 As Variant, Firstaddr2 As Variant
Sheets(3).Select
With ActiveWorkbook.Sheets(3)
Mr = .Cells(65336, 3).End(xlUp).Row
For x = 2 To 5
For h = 2 To Mr
If .Cells(h, 12) <> "" Then Rows(h).Delete Shift:=xlUp
Mr = .Cells(65336, 3).End(xlUp).Row
Next h
Next x
Sheets(2).Select
T = 2
For x = 1 To 100
For h = T To Mr
With Sheets(2).Columns(1)
Set MySearch1 = .Find(What:=Sheets(3).Cells(h, 3), After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not MySearch1 Is Nothing And Sheets(3).Cells(h, 12) <> "循环标识" Then
Firstaddr1 = MySearch1.Address
Set MySearch2 = .FindNext(After:=MySearch1)
y = h + 1
W = 0
Do While Not MySearch2 Is Nothing And MySearch2.Address <> Firstaddr1
MySR1 = MySearch2.Row
With Sheets(3)
.Rows(h).Copy
.Rows(h).Insert Shift:=xlUp
.Cells(y, 3).Interior.ColorIndex = 39
.Cells(y, 6) = Sheets(2).Cells(MySR1, 4)
.Cells(y, 9) = Sheets(2).Cells(MySR1, 5)
.Cells(y, 10) = Sheets(2).Cells(MySR1, 6)
.Cells(y, 11) = Sheets(2).Cells(MySR1, 3)
.Cells(y, 12) = "循环标识"
W = W + 1
End With
Set MySearch2 = .FindNext(After:=MySearch2)
Loop
Set MySearch2 = Nothing
Set MySearch1 = Nothing
End If
End With
T = y + W
Mr = .Cells(65336, 3).End(xlUp).Row
Next h
T = y + W
Mr = .Cells(65336, 3).End(xlUp).Row
Next x
'MsgBox h & "_" & Mr
End With
End Sub
上述代码中,若没有“ For x = 1 To 100”这个语句,则不会从sheets(2)中搜索出符合sheets(3)中的证券权益,为何? |
|