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
| For Each Cell In FL1.Range("B2:B" & DerLig1)
With FL2.Range("B2:B" & DerLig2)
Set n = .Find(Cell, LookIn:=xlValues, LookAt:=xlWhole)
If n Is Nothing Then
FL1.Cells(Cell.Row, DerCol1 + 1).Value = "new name"
Else
firstaddress = n.Address
NameAddr = Mid(firstaddress, 4, 7) - 1
With FL2.Range("C" & NameAddr & ":C" & DerLig2)
Set p = .Find((Cell.Offset(0, 1).Value), LookIn:=xlValues, LookAt:=xlWhole)
If p Is Nothing Then
FL1.Cells(Cell.Row, DerCol1 + 1).Value = "new proj"
Else
secondaddress = p.Address
ProjAddr = Mid(secondaddress, 4, 7) - 1
With FL2.Range("F" & ProjAddr & ":F" & DerLig2)
Set m = .Find((Cell.Offset(0, 4).Value), LookIn:=xlValues, LookAt:=xlWhole)
If m Is Nothing Then
FL1.Cells(Cell.Row, DerCol1 + 1).Value = "new month"
Else
If Cell.Offset(0, 4).Value = m.Offset(0, 0).Value Then
FL1.Cells(Cell.Row, DerCol1 + 1).Value = m.Offset(0, 1).Value
Else
MonthAddr = m.Address
Do
Set m = .FindNext(m)
If (Cell.Offset(0, 1).Value = p.Offset(0, 0).Value) And (Cell.Offset(0, 4).Value = m.Offset(0, 0).Value) Then
FL1.Cells(Cell.Row, DerCol1 + 1).Value = m.Offset(0, 0).Value
Exit Do
End If
Loop While Not (m Is Nothing And m.Address <> MonthAddr)
endloop = False
End If
End If
End With
End If
End With
End If
End With
Set m = Nothing
Set n = Nothing
Set p = Nothing
Next |
Partager