Attribute VB_Name = "GRT_GA_Fault"
Option Explicit
Dim lastrow As Long
Dim weeky, houry As Integer
Dim cl, cell As Range
Dim Text, Tcal, Tright As Double
'Text external temperature, Tcal calculated temperature, Tright correct calculated temperature
Dim Thigh, Tlow, Textlow, Texthigh, line As Double
'Thigh high temperature control, Tlow low temperature control, Textlow low temperature external, Texthigh high temperature external
Sub Fault()
'18052018
'this macro is done to detect errors coming from incorrect setting calculation
'it uses the curves for setting internal temperature of hot water circuit, for night, weekends, ...

Thigh = 65: Tlow = 40: Textlow = 3: Texthigh = 20
Windows("stat.Heizung Süd-West Geb.010 Check 1.xlsx").Activate 'change for the correct name
Columns("B:B").Select
Selection.Find(What:="Zeit", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
Set cl = ActiveCell.Offset(1, 0)
cl.Offset(-1, 4).Value = "Tright"
cl.Offset(-1, 5).Value = "Offset"
cl.Offset(-1, 6).Value = "Control"
cl.Select
Range(Selection, Selection.End(xlDown)).Select
For Each cell In Selection
    weeky = Weekday(cell.Value)
    houry = hour(cell.Value)
    Text = cell.Offset(0, 3).Value 
    line = Thigh - (Thigh - Tlow) / (Texthigh - Textlow) * Text
    Call calculate((weeky), (houry), (Text))
    cell.Offset(0, 4).Value = Tright
    cell.Offset(0, 4).NumberFormat = "0"

Next cell
lastrow = Range("b" & Rows.count).End(xlUp).Row
Range("G8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=+RC[-4]-RC[-3]"
Range("H8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=+RC[-2]-RC[-4]"
Range("G8:H8").Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Selection.AutoFill Destination:=Range("G8:H" & lastrow)

End Sub

Sub calculate(weeky As Integer, houry1 As Integer, Text1 As Double)
If weeky = 1 Or weeky = 7 Then 'weekend
    Select Case Text1
        Case Is <= Textlow
            Tright = Thigh - 10
        Case Is >= Texthigh
            Tright = Tlow - 10
        Case Else
            Tright = line - 10
    End Select
Else
    If houry1 >= 5 And houry1 <= 20 Then 'hourly period of a day
        Select Case Text1
            Case Is <= Textlow
                Tright = Thigh
            Case Is >= Texthigh
                Tright = Tlow
            Case Else
                Tright = line
        End Select
    Else 'night
        Select Case Text1
            Case Is <= Textlow
                Tright = Thigh - 10
            Case Is >= Texthigh
                Tright = Tlow - 10
            Case Else
                Tright = line - 10
        End Select
    End If
End If

End Sub