While Sheet3.Cells(b, 1) <> ""
If Application.WorksheetFunction.IsNA(Application.VLookup(Sheet3.Cells(b, 13).Value, Sheet1.Range("T2:T" & x), 1, 0)) = True Then
Sheet3.Range("B" & d & ":T" & d).Cut
ActiveSheet.Paste Destination:=Sheet4.Range("B" & d & ":B" & d)
Sheet3.Rows(d).Delete
d = d + 1
End If
b = b + 1
Wend
a = 2
c = 1
b = 9
'Fill rows
While Sheet1.Cells(a, 1) <> ""
If Application.WorksheetFunction.IsNA(Application.VLookup(Sheet3.Cells(b, 13).Value, Sheet1.Range("T2:T" & x), 1, 0)) = False Then
Sheet3.Cells(b, 7) = Sheet1.Cells(a, i)
Sheet3.Cells(b, 7) = Sheet3.Cells(b, 7) * 100000
Sheet3.Cells(b, 7).NumberFormat = "#,##0.00"
Sheet3.Cells(b, 7) = Sheet3.Cells(b, 7) / 100000 'adjust to currency format
Sheet3.Cells(b, 8) = Sheet1.Cells(a, j)
Sheet3.Cells(b, 8) = Sheet3.Cells(b, 8) * 100000
Sheet3.Cells(b, 8).NumberFormat = "#,##0.00"
Sheet3.Cells(b, 8) = Sheet3.Cells(b, 8) / 100000 'adjust to currency format
Sheet3.Cells(b, 9) = Sheet3.Cells(b, 7) - Sheet3.Cells(b, 8)
Sheet3.Cells(b, 9) = Sheet3.Cells(b, 9) * 100000
Sheet3.Cells(b, 9).NumberFormat = "#,##0.00"
Sheet3.Cells(b, 9) = Sheet3.Cells(b, 9) / 100000 'adjust to currency format
Sheet3.Cells(b, 10) = Sheet1.Cells(a, k) - Sheet1.Cells(a, l)
Sheet3.Cells(b, 10) = Sheet3.Cells(b, 10) * 100000
Sheet3.Cells(b, 10).NumberFormat = "#,##0.00"
Sheet3.Cells(b, 10) = Sheet3.Cells(b, 10) / 100000 'adjust to currency format
End If
Sheet3.Cells(b, 11).Value = (Day(Sheet3.Cells(b, 11).Value)) & "/" & _
(Month(Sheet3.Cells(b, 11).Value)) & "/" & (Year(Sheet3.Cells(b, 11).Value))
With Sheet3.Cells(b, 11)
.Value = Format(Sheet3.Cells(b, 11), "dd/mm/yyyy")
.NumberFormat = "@" 'adjust to correct date format
End With
b = b + 1
If Application.WorksheetFunction.IsNA(Application.VLookup(Sheet1.Cells(x, "T").Value, Sheet3.Range("M2:M" & x), 1, 0)) = True Then
Sheet3.Cells(b, 1) = Sheet1.Cells(a, d)
Sheet3.Cells(b, 2) = Sheet1.Cells(a, e)
Sheet3.Cells(b, 3) = Sheet1.Cells(a, f)
Sheet3.Cells(b, 4) = Sheet1.Cells(a, q)
Sheet3.Cells(b, 5) = Sheet1.Cells(a, g)
Sheet3.Cells(b, 6) = Sheet1.Cells(a, h)
Sheet3.Cells(b, 7) = Sheet1.Cells(a, i)
Sheet3.Cells(b, 7) = Sheet3.Cells(b, 7) * 100000
Sheet3.Cells(b, 7).NumberFormat = "#,##0.00"
Sheet3.Cells(b, 7) = Sheet3.Cells(b, 7) / 100000 'adjust to currency format
Sheet3.Cells(b, 8) = Sheet1.Cells(a, j)
Sheet3.Cells(b, 8) = Sheet3.Cells(b, 8) * 100000
Sheet3.Cells(b, 8).NumberFormat = "#,##0.00"
Sheet3.Cells(b, 8) = Sheet3.Cells(b, 8) / 100000 'adjust to currency format
Sheet3.Cells(b, 9) = Sheet3.Cells(b, 7) - Sheet3.Cells(b, 8)
Sheet3.Cells(b, 9) = Sheet3.Cells(b, 9) * 100000
Sheet3.Cells(b, 9).NumberFormat = "#,##0.00"
Sheet3.Cells(b, 9) = Sheet3.Cells(b, 9) / 100000 'adjust to currency format
Sheet3.Cells(b, 10) = Sheet1.Cells(a, k) - Sheet1.Cells(a, l)
Sheet3.Cells(b, 10) = Sheet3.Cells(b, 10) * 100000
Sheet3.Cells(b, 10).NumberFormat = "#,##0.00"
Sheet3.Cells(b, 10) = Sheet3.Cells(b, 10) / 100000 'adjust to currency format
End If
Sheet3.Cells(b, 11).Value = (Day(Sheet3.Cells(b, 11).Value)) & "/" & _
(Month(Sheet3.Cells(b, 11).Value)) & "/" & (Year(Sheet3.Cells(b, 11).Value))
With Sheet3.Cells(b, 11)
.Value = Format(Sheet3.Cells(b, 11), "dd/mm/yyyy")
.NumberFormat = "@" 'adjust to correct date format
End With
b = b + 1
a = a + 1
Wend