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
| Sub Lookup()
Dim NumRow, BLo, TRa, RaT, i As Long, j As Long
With Worksheets("Blotter")
NumRow = .Cells(.Rows.Count, "B").End(xlUp).Row
BLo = Range("A2", "AI" & NumRow)
End With
With Worksheets("Transco")
NumRow = .Range("A" & .Rows.Count).End(xlUp).Row
TRa = .Range("A2", "G" & NumRow)
End With
For i = 1 To UBound(BLo, 1)
For j = 1 To UBound(TRa, 1)
If BLo(i, 3) = TRa(j, 1) Then BLo(i, 30) = TRa(j, 2)
If BLo(i, 3) = TRa(j, 1) Then BLo(i, 31) = TRa(j, 3)
If BLo(i, 3) = TRa(j, 1) Then BLo(i, 32) = TRa(j, 4)
If BLo(i, 3) = TRa(j, 1) Then BLo(i, 33) = TRa(j, 5)
Next j
Next i
With Worksheets("Rates")
NumRow = .Range("A" & .Rows.Count).End(xlUp).Row
RaT = .Range("A2", "C" & NumRow)
End With
For i = 1 To UBound(BLo, 1)
For j = 1 To UBound(RaT, 1)
If BLo(i, 7) = RaT(j, 1) Then BLo(i, 34) = RaT(j, 3)
Exit For
Next j
BLo(i, 35) = BLo(i, 8) / BLo(i, 34)
Next i
Sheets("Blotter").Range("A2").Resize(UBound(BLo, 1), UBound(BLo, 2)) = BLo
End Sub |
Partager