1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
| Sub test()
Dim i As Double
Dim j As Integer
Dim derli As Double
Dim derli2 As Integer
Dim wk As Workbook
Dim wk2 As Workbook
Dim sh As Worksheet
Dim sh2 As Worksheet
Dim k As Integer
k = 6
Set wk = Workbooks("x.xls")
Workbooks("x.xls").Activate
Set sh = Worksheets("y")
Set wk2 = Workbooks("xx.xls")
Workbooks("xx").Activate
Set sh2 = Worksheets("yy")
Workbooks("x.xls").Activate
derli = sh.Columns(1).Find("*", , , , , xlPrevious).Row
Workbooks("xx.xls").Activate
derli2 = sh2.Columns(1).Find("*", , , , , xlPrevious).Row
Workbooks("x.xls").Activate
For i = 2 To derli
For j = 6 To derli2
Workbooks("x.xls").Activate
Dim tab1(1 To 8, 1 To 1) As Variant
If Mid(Range("am" & i), 4) = CStr(sh2.Range("aa" & j).Value) Then
tab1(1, 1) = Mid(sh.Range("Am" & i), 4).Value
tab1(2, 1) = sh.Range("s" & i).Value
tab1(3, 1) = sh.Range("bj" & i).Value
tab1(4, 1) = sh.Range("bs" & i).Value
tab1(5, 1) = sh.Range("da" & i).Value
tab1(6, 1) = sh.Range("dl" & i).Value
tab1(7, 1) = sh.Range("dq" & i).Value
tab1(8, 1) = sh.Range("dr" & i).Value
k = k + 1
End If
wk2.Activate
sh2.Activate
Range(Cells(k, 86), Cells(k, 93)).Value = Application.Transpose(tab1)
Next j
Next i
End Sub |
Partager