Sub Extract()
Dim I, J, K, STARTROW1, STARTROW2, ENDROW1, ENDROW2, COUNTTENANT As Integer
Sheets("Output").Select
Range("A10000").Select
Selection.End(xlUp).Select
ENDROW1 = ActiveCell.Row - 4
STARTROW1 = 1
Sheets("Lease Comparison").Select
Range("A10000").Select
Selection.End(xlUp).Select
ENDROW2 = ActiveCell.Row - 2
STARTROW2 = 1
'CLEARS OUT EXISTING OUTPUT SHEETS
Sheets("Output").Select
Range("D9.H41").ClearContents
For I = STARTROW1 To ENDROW1
Sheets("Output").Select
Range("A5").Select
PROPCODE = ActiveCell.Offset(I, 0).Value
MAXVALUE = 0
MINVALUE = 99999
MINDATE = 99999
MAXDATE = 0
MINBDATE = 99999
MAXBDATE = 0
MINEDATE = 99999
MAXEDATE = 0
If PROPCODE = "" Then
GoTo 1000
Else
End If
For J = STARTROW2 To ENDROW2
Sheets("Lease Comparison").Select
Range("A2").Select
If ActiveCell.Offset(J, 0) = PROPCODE Then
RENTVALUE = ActiveCell.Offset(J, 77).Value
If RENTVALUE < MINVALUE Then
MINVALUE = RENTVALUE
Else
End If
If RENTVALUE > MAXVALUE Then
MAXVALUE = RENTVALUE
Else
End If
NEXTREVIEW = ActiveCell.Offset(J, 70).Value
If NEXTREVIEW < MINDATE Then
MINDATE = NEXTREVIEW
Else
End If
If NEXTREVIEW > MAXDATE Then
MAXDATE = NEXTREVIEW
Else
End If
BREAKDATE = ActiveCell.Offset(J, 59).Value
If BREAKDATE < MINBDATE Then
MINBDATE = BREAKDATE
Else
End If
If BREAKDATE > MAXBDATE Then
MAXBDATE = BREAKDATE
Else
End If
EXPIRYDATE = ActiveCell.Offset(J, 64).Value
If EXPIRYDATE < MINEDATE Then
MINEDATE = EXPIRYDATE
Else
End If
If EXPIRYDATE > MAXEDATE Then
MAXEDATE = EXPIRYDATE
Else
End If
Else
End If
Next J
Sheets("Output").Select
Range("A5").Select
If MAXVALUE = MINVALUE Then
RENTVALUE = Format$(MINVALUE, "£#,##0.00")
Else
RENTVALUE = Format$(MINVALUE, "£#,##0.00") & " - " & Format$(MAXVALUE, "£#,##0.00")
End If
ActiveCell.Offset(I, 3) = RENTVALUE
If MAXDATE = MINDATE Then
NEXTREVIEW = Format(MINDATE, "mmm-yy")
Else
NEXTREVIEW = Format(MINDATE, "mmm-yy") & " - " & Format(MAXDATE, "mmm-yy")
End If
ActiveCell.Offset(I, 4) = NEXTREVIEW
If MAXBDATE = MINBDATE Then
BREAKDATE = Format(MINBDATE, "mmm-yy")
Else
BREAKDATE = Format(MINBDATE, "mmm-yy") & " - " & Format(MAXBDATE, "mmm-yy")
End If
ActiveCell.Offset(I, 5) = BREAKDATE
If MAXEDATE = MINEDATE Then
EXPIRYDATE = Format(MINEDATE, "mmm-yy")
Else
EXPIRYDATE = Format(MINEDATE, "mmm-yy") & " - " & Format(MAXEDATE, "mmm-yy")
End If
ActiveCell.Offset(I, 6) = EXPIRYDATE
1000
Next I
End Sub