Sub Report_Z_1()
Dim Cell As Range
Dim Rng As Range
Dim C As Range
Dim R As Range
Dim S As Range
Dim CellCell As Range
Dim RngRng As Range
Dim CC As Range
Dim RR As Range
Dim SS As Range
Dim CellCellCell As Range
Dim RngRngRng As Range
Dim CCC As Range
Dim RRR As Range
Dim SSS As Range
Application.EnableEvents = False
If Not Worksheets("3b. Analyse migratieresultaten").Range("G61").Value = Worksheets("Settings").Range("A1").Value Then
'Debet data
With Worksheets("Z1")
Set CellCell = .Columns(1).Find(What:="Financial Totals For Side 1", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Set RngRng = CellCell.Offset(2)
If IsEmpty(RngRng) Then
MsgBox "The financials for Side 1 are empty"
Else
Set RngRng = .Range(CellCell, CellCell.End(xlDown))
If RngRng.Count = 3 Then
Set RngRng = CellCell.Offset(2)
Else
Set RngRng = .Range(CellCell.Offset(2), CellCell.Offset(2).End(xlDown))
End If
End If
RngRng.Offset(, 5).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],'AAB FX EOM Rates'!C3:C8,6,FALSE)),1,VLOOKUP(RC[-5],'AAB FX EOM Rates'!C3:C8,6,FALSE))"
RngRng.Offset(, 6).FormulaR1C1 = "=RC[-5]/RC[-1]"
RngRng.Offset(, 7).FormulaR1C1 = "=RC[-5]/RC[-2]"
CellCell.Offset(, 5).Value = "Financial Totals For Side 1 in Euros"
CellCell.Offset(1, 5).Value = "Exchange Rate"
CellCell.Offset(1, 6).Value = "Debit"
CellCell.Offset(1, 7).Value = "Credit"
End With
With Worksheets("Z1")
Set CC = .Columns(6).Find(What:="Financial Totals For Side 1 in Euros", After:=.Cells(1, 6), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Set RR = .Range(CC.Offset(2), CC.End(xlDown))
Set SS = CC.End(xlDown).Offset(1, 1)
SS.Resize(, 2).FormulaR1C1 = "=SUM(R[-" & RR.Rows.Count & "]C:R[-1]C)"
End With
' Credit data
With Worksheets("Z1")
Set CellCellCell = .Columns(1).Find(What:="Financial Totals For Side 2", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Set RngRngRng = CellCellCell.Offset(2)
If IsEmpty(RngRngRng) Then
MsgBox "The financials for Side 2 are empty"
Else
Set RngRngRng = .Range(CellCellCell, CellCellCell.End(xlDown))
If RngRngRng.Count = 3 Then
Set RngRngRng = CellCellCell.Offset(2)
Else
Set RngRngRng = .Range(CellCellCell.Offset(2), CellCellCell.Offset(2).End(xlDown))
End If
End If
RngRngRng.Offset(, 5).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],'AAB FX EOM Rates'!C3:C8,6,FALSE)),1,VLOOKUP(RC[-5],'AAB FX EOM Rates'!C3:C8,6,FALSE))"
RngRngRng.Offset(, 6).FormulaR1C1 = "=RC[-5]/RC[-1]"
RngRngRng.Offset(, 7).FormulaR1C1 = "=RC[-5]/RC[-2]"
CellCellCell.Offset(, 5).Value = "Financial Totals For Side 2 in Euros"
CellCellCell.Offset(1, 5).Value = "Exchange Rate"
CellCellCell.Offset(1, 6).Value = "Debit"
CellCellCell.Offset(1, 7).Value = "Credit"
End With
With Worksheets("Z1")
Set CCC = .Columns(6).Find(What:="Financial Totals For Side 2 in Euros", After:=.Cells(1, 6), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Set RRR = .Range(CCC.Offset(2), CCC.End(xlDown))
Set SSS = CCC.End(xlDown).Offset(1, 1)
SSS.Resize(, 2).FormulaR1C1 = "=SUM(R[-" & RRR.Rows.Count & "]C:R[-1]C)"
End With
'difference data
With Worksheets("Z1")
Set Cell = .Columns(1).Find(What:="Delta Between Side1 and Side2 ", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Set Rng = Cell.Offset(2)
If IsEmpty(Rng) Then
MsgBox "The Reconciliation does not result in a financial difference between Side 1 and Side 2"
Else
Set Rng = .Range(Cell, Cell.End(xlDown))
If Rng.Count = 3 Then
Set Rng = Cell.Offset(2)
Else
Set Rng = .Range(Cell.Offset(2), Cell.Offset(2).End(xlDown))
End If
End If
Rng.Offset(, 5).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],'AAB FX EOM Rates'!C3:C8,6,FALSE)),1,VLOOKUP(RC[-5],'AAB FX EOM Rates'!C3:C8,6,FALSE))"
Rng.Offset(, 6).FormulaR1C1 = "=VLOOKUP(RC[-6]," & RngRng.Resize(, 3) & ",2,0)-VLOOKUP(RC[-6]," & RngRngRng.Resize(, 3) & ",3,0)"
Rng.Offset(, 7).FormulaR1C1 = "=VLOOKUP(RC[-7]," & RngRng.Resize(, 3) & ",3,FALSE)-VLOOKUP(RC[-7]," & RngRngRng.Resize(, 3) & ",2,FALSE)"
Cell.Offset(, 5).Value = "Delta Between Side1 and Side2 in Euros"
Cell.Offset(1, 5).Value = "Exchange Rate"
Cell.Offset(1, 6).Value = "Debit"
Cell.Offset(1, 7).Value = "Credit"
End With
With Worksheets("Z1")
Set C = .Columns(6).Find(What:="Delta Between Side1 and Side2 in Euros", After:=.Cells(1, 6), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Set R = .Range(C.Offset(2), C.End(xlDown))
Set S = C.End(xlDown).Offset(1, 1)
S.Resize(, 2).FormulaR1C1 = "=SUM(R[-" & R.Rows.Count & "]C:R[-1]C)"
End With
Worksheets("3b. Analyse migratieresultaten").Range("I61").Value = SS.Value
Worksheets("3b. Analyse migratieresultaten").Range("J61").Value = SS.Offset(, 1).Value
Worksheets("3b. Analyse migratieresultaten").Range("L61").Value = S.Value
Worksheets("3b. Analyse migratieresultaten").Range("M61").Value = S.Offset(, 1).Value
Else
Worksheets("3b. Analyse migratieresultaten").Range("I61").Value = ""
Worksheets("3b. Analyse migratieresultaten").Range("J61").Value = ""
Worksheets("3b. Analyse migratieresultaten").Range("L61").Value = ""
Worksheets("3b. Analyse migratieresultaten").Range("M61").Value = ""
End If
Application.EnableEvents = True
End Sub