Sub callfinaltest7()
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next
finaltest7
On Error Resume Next
addproblem
On Error Resume Next
finaltouch
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Sub finaltest7()
removeproblem
Dim tempuintu As Integer
Dim temppos As String
Dim tempint As Integer
Dim tempint2 As Integer
Dim k As Integer
Dim temppos2 As String
Dim l As Integer
l = 0
Dim m As Integer
m = 1
Dim q As Integer
q = 0
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Sheets("temp_data_2").Delete
ActiveWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "temp_data_2"
On Error Resume Next
ActiveWorkbook.Sheets("temp_data").Delete
ActiveWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "temp_data"
Sheets(1).Select
temppos = "C5"
temppos2 = temppos
Range(temppos).Select
While ActiveCell.Value <> ""
If InStr(ActiveCell.Value, "Total") <> 0 Then
If ActiveCell.Value = "TOTAL" Then
Exit Sub
End If
'Sheets("X").Select
'Range(temppos2).Select
'k = 0
'While InStr(ActiveCell.Value, "Total") = 0
'k = k + 1
'ActiveCell.Offset(1, 0).Select
'Wend
'k = k + l + 4 + m
'm = m + 1
'l = l + k
'MsgBox (l)
q = 0
tempint = ActiveCell.Column + 1
tempint2 = ActiveCell.Row + 1
Sheets("temp_data_2").Select
Range("A1").Select
ActiveCell.Value = tempint
Range("A2").Select
ActiveCell.Value = tempint2
Range("A3").Select
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(ADDRESS(1,R[-2]C,4),""1"","""")&R[-1]C"
temppos = ActiveCell.Value
'MsgBox (temppos)
Sheets("Summary").Select
Range(temppos).Select
temppos2 = temppos
'MsgBox (temppos2)
'firstcolnum = CInt((Range(selectedarea & 1).Column))
'special test
'special test end
Else
If ActiveCell.Value = "TOTAL" Then
Exit Sub
End If
'testing start
If q = 0 Then
Sheets("X").Select
Range(temppos2).Select
While InStr(ActiveCell.Value, "Total") = 0 Or ActiveCell.Value = "Total Orissa State"
ActiveCell.Offset(1, 0).Select
Wend
k = ActiveCell.Row
'MsgBox (k)
End If
q = 1
Sheets(1).Select
Range(temppos).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C40:R124C51,MATCH(RC[-1],X!R5C3:R124C3,0)+1,FALSE),0)"
Range(temppos).Select
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R124C39,MATCH(RC[-2],X!R5C3:R124C3,0)+1,FALSE),0)"
Range(temppos).Select
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range(temppos).Select
Dim temp As Double
'MsgBox (temppos2)
'MsgBox (k)
Sheets("temp_data").Select
Range("A1").Select
On Error Resume Next
ActiveCell.FormulaR1C1 = _
"=MATCH(TEXT(Summary!R1C9,""mmm"")&""-""&TEXT(Summary!R1C9,""yy""),X!R[3]C[3]:R[3]C[38],0)+3"
ActiveCell.Offset(0, 1).Range("A1").Select
On Error Resume Next
ActiveCell.FormulaR1C1 = "=COLUMN(INDIRECT(""C6""))+RC[-1]"
ActiveCell.Offset(0, 1).Range("A1").Select
On Error Resume Next
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(ADDRESS(1,RC[-2],4),""1"","""")"
ActiveCell.Offset(0, 1).Range("A1").Select
On Error Resume Next
ActiveCell.FormulaR1C1 = "=RC[-1] "
ActiveCell.Value = ActiveCell.Value & k
temp2 = ActiveCell.Value
Sheets("X").Select
Range(temp2).Select
temp = ActiveCell.Value
Sheets("temp_data").Select
Range("E1").Select
ActiveCell.Value = temp
Sheets("Summary").Select
Range(temppos).Select
ActiveCell.Offset(0, 4).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R124C39,MATCH(RC[-4],X!R5C3:R124C3,0)+1,FALSE),0)/ " & temp & " ,0)"
Range(temppos).Select
ActiveCell.Offset(0, 5).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R124C39,MATCH(RC[-5],X!R5C3:R124C3,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R124C39,MATCH(RC[-5],X!R5C3:R124C3,0)+1,FALSE)-1,0)"
'ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R17C27,MATCH(RC[-3],X!R5C3:R17C3)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R17C27,MATCH(RC[-3],X!R5C3:R17C3)+1,FALSE)-1,0)"
Range(temppos).Select
ActiveCell.Offset(0, 6).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R124C39,MATCH(RC[-6],X!R5C3:R124C3,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R124C39,MATCH(RC[-6],X!R5C3:R124C3,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R124C39,MATCH(RC[-6],Y!R5C3:R124C3,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R124C39,MATCH(RC[-6],Z!R5C3:R124C3,0)+1,FALSE),0)),0)"
Dim i As Integer
i = 1
Dim result As Long
result = 0
Dim str2 As String
Sheets("sheet1").Select
Range(temppos).Select
str2 = """" & ActiveCell.Value & """"
'MsgBox (str2)
For i = 0 To 2
Range(temppos).Select
ActiveCell.Offset(0, 7).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )
' MsgBox (ActiveCell.Value)
result = result + CInt(ActiveCell.Value)
Next i
Range(temppos).Select
ActiveCell.Offset(0, 7).Select
ActiveCell.Value = result
Range(temppos).Select
ActiveCell.Offset(0, 8).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R124C39,MATCH(RC[-8],X!R5C3:R124C3,0)+1,FALSE),0),0)"
Range(temppos).Select
str2 = """" & ActiveCell.Value & """"
'MsgBox (str2)
result = 0
For i = 0 To 11
Range(temppos).Select
ActiveCell.Offset(0, 9).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )
result = result + CInt(ActiveCell.Value)
Next i
Range(temppos).Select
ActiveCell.Offset(0, 9).Select
ActiveCell.Value = result
Range(temppos).Select
ActiveCell.Offset(0, 10).Select
'Dim result As Long
result = 0
Dim result1 As Long
Dim result2 As Long
result1 = 0
result2 = 0
'Dim str2 As String
Sheets("sheet1").Select
Range(temppos).Select
str2 = """" & ActiveCell.Value & """"
'MsgBox (str2)
For i = 0 To 2
Range(temppos).Select
ActiveCell.Offset(0, 10).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )
result1 = result1 + CInt(ActiveCell.Value)
Next i
Range(temppos).Select
ActiveCell.Offset(0, 10).Select
ActiveCell.Value = result1
For i = 0 To 2
Range(temppos).Select
ActiveCell.Offset(0, 10).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )
result2 = result2 + CInt(ActiveCell.Value)
' MsgBox (result2)
Next i
Range(temppos).Select
ActiveCell.Offset(0, 10).Select
' MsgBox (result1 & " " & result2)
ActiveCell.Value = result1 / result2 - 1
result1 = 0
result2 = 0
Range(temppos).Select
ActiveCell.Offset(0, 11).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R124C39,MATCH(RC[-11],X!R5C3:R124C3,0)+1,FALSE),0),0)"
result1 = ActiveCell.Value
i = 1
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R124C39,MATCH(RC[-11],X!R5C3:R124C3,0)+1,FALSE),0),0)"
result2 = ActiveCell.Value
ActiveCell.Value = result1 / result2 - 1
'end action
Range(temppos).Select
ActiveCell.Offset(0, 12).Select
result1 = 0
result2 = 0
Sheets("sheet1").Select
Range(temppos).Select
str2 = """" & ActiveCell.Value & """"
For i = 0 To 11
Range(temppos).Select
ActiveCell.Offset(0, 12).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )
' MsgBox (ActiveCell.Value)
result1 = result1 + CInt(ActiveCell.Value)
Next i
'MsgBox (result1)
For i = 0 To 11
Range(temppos).Select
ActiveCell.Offset(0, 12).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )
' MsgBox (ActiveCell.Value)
result2 = result2 + CInt(ActiveCell.Value)
Next i
' MsgBox (result2)
Range(temppos).Select
ActiveCell.Offset(0, 12).Select
ActiveCell.Value = result1 / result2 - 1
result1 = 0
result2 = 0
'testing done
Sheets("Summary").Select
Range(temppos).Select
ActiveCell.Offset(1, 0).Select
temppos = Replace(ActiveCell.Address, "quot;, "")
End If
Wend
'MsgBox (ActiveCell.Address)
End Sub
Sub callfinaltest8()
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next
finaltest8
On Error Resume Next
addproblem
On Error Resume Next
finaltouch
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Sub finaltest8()
removeproblem
Dim tempuintu As Integer
Dim temppos As String
Dim tempint As Integer
Dim tempint2 As Integer
Dim k As Integer
Dim temppos2 As String
Dim l As Integer
l = 0
Dim m As Integer
m = 1
Dim q As Integer
q = 0
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Sheets("temp_data_2").Delete
ActiveWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "temp_data_2"
On Error Resume Next
ActiveWorkbook.Sheets("temp_data").Delete
ActiveWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "temp_data"
Sheets(1).Select
temppos = "C5"
temppos2 = temppos
Range(temppos).Select
While ActiveCell.Value <> ""
If InStr(ActiveCell.Value, "Total") <> 0 Then
If ActiveCell.Value = "TOTAL" Then
Exit Sub
End If
'Sheets("X").Select
'Range(temppos2).Select
'k = 0
'While InStr(ActiveCell.Value, "Total") = 0
'k = k + 1
'ActiveCell.Offset(1, 0).Select
'Wend
'k = k + l + 4 + m
'm = m + 1
'l = l + k
'MsgBox (l)
q = 0
tempint = ActiveCell.Column + 1
tempint2 = ActiveCell.Row + 1
Sheets("temp_data_2").Select
Range("A1").Select
ActiveCell.Value = tempint
Range("A2").Select
ActiveCell.Value = tempint2
Range("A3").Select
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(ADDRESS(1,R[-2]C,4),""1"","""")&R[-1]C"
temppos = ActiveCell.Value
'MsgBox (temppos)
Sheets("Summary").Select
Range(temppos).Select
temppos2 = temppos
'MsgBox (temppos2)
'firstcolnum = CInt((Range(selectedarea & 1).Column))
'special test
'special test end
Else
If ActiveCell.Value = "TOTAL" Then
Exit Sub
End If
'testing start
If q = 0 Then
Sheets("Y").Select
Range(temppos2).Select
While InStr(ActiveCell.Value, "Total") = 0
ActiveCell.Offset(1, 0).Select
Wend
k = ActiveCell.Row
'MsgBox (k)
End If
q = 1
Sheets(1).Select
Range(temppos).Select
ActiveCell.Offset(0, 13).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C40:R124C51,MATCH(RC[-13],Y!R5C3:R124C3,0)+1,FALSE),0)"
Range(temppos).Select
ActiveCell.Offset(0, 14).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R124C39,MATCH(RC[-14],Y!R5C3:R124C3,0)+1,FALSE),0)"
Range(temppos).Select
ActiveCell.Offset(0, 15).Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range(temppos).Select
Dim temp As Double
'MsgBox (temppos2)
'MsgBox (k)
Sheets("temp_data").Select
Range("A1").Select
On Error Resume Next
ActiveCell.FormulaR1C1 = _
"=MATCH(TEXT(Summary!R1C18,""mmm"")&""-""&TEXT(Summary!R1C18,""yy""),Y!R[3]C[3]:R[3]C[38],0)+3"
ActiveCell.Offset(0, 1).Range("A1").Select
On Error Resume Next
ActiveCell.FormulaR1C1 = "=COLUMN(INDIRECT(""C6""))+RC[-1]"
ActiveCell.Offset(0, 1).Range("A1").Select
On Error Resume Next
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(ADDRESS(1,RC[-2],4),""1"","""")"
ActiveCell.Offset(0, 1).Range("A1").Select
On Error Resume Next
ActiveCell.FormulaR1C1 = "=RC[-1] "
ActiveCell.Value = ActiveCell.Value & k
temp2 = ActiveCell.Value
Sheets("Y").Select
Range(temp2).Select
temp = ActiveCell.Value
Sheets("temp_data").Select
Range("E1").Select
ActiveCell.Value = temp
Sheets("Summary").Select
Range(temppos).Select
ActiveCell.Offset(0, 16).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R124C39,MATCH(RC[-16],Y!R5C3:R124C3,0)+1,FALSE),0)/ " & temp & " ,0)"
Range(temppos).Select
ActiveCell.Offset(0, 17).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R124C39,MATCH(RC[-17],Y!R5C3:R124C3,0)+1,FALSE)/HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy"")-1,Y!R4C4:R124C39,MATCH(RC[-17],Y!R5C3:R124C3,0)+1,FALSE)-1,0)"
'ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R17C27,MATCH(RC[-3],X!R5C3:R17C3)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R17C27,MATCH(RC[-3],X!R5C3:R17C3)+1,FALSE)-1,0)"
Range(temppos).Select
ActiveCell.Offset(0, 18).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R124C39,MATCH(RC[-18],Y!R5C3:R124C3,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),X!R4C4:R124C39,MATCH(RC[-18],X!R5C3:R124C3,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R124C39,MATCH(RC[-18],Y!R5C3:R124C3,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Z!R4C4:R124C39,MATCH(RC[-18],Z!R5C3:R124C3,0)+1,FALSE),0)),0)"
'testing done
Sheets("Summary").Select
Range(temppos).Select
ActiveCell.Offset(1, 0).Select
temppos = Replace(ActiveCell.Address, "quot;, "")
End If
Wend
'MsgBox (ActiveCell.Address)
End Sub
Sub callfinaltest9()
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next
finaltest9
On Error Resume Next
addproblem
On Error Resume Next
finaltouch
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Sub finaltest9()
removeproblem
Dim tempuintu As Integer
Dim temppos As String
Dim tempint As Integer
Dim tempint2 As Integer
Dim k As Integer
Dim temppos2 As String
Dim l As Integer
l = 0
Dim m As Integer
m = 1
Dim q As Integer
q = 0
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Sheets("temp_data_2").Delete
ActiveWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "temp_data_2"
On Error Resume Next
ActiveWorkbook.Sheets("temp_data").Delete
ActiveWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "temp_data"
Sheets(1).Select
temppos = "C5"
temppos2 = temppos
Range(temppos).Select
While ActiveCell.Value <> ""
If InStr(ActiveCell.Value, "Total") <> 0 Then
If ActiveCell.Value = "TOTAL" Then
Exit Sub
End If
'Sheets("X").Select
'Range(temppos2).Select
'k = 0
'While InStr(ActiveCell.Value, "Total") = 0
'k = k + 1
'ActiveCell.Offset(1, 0).Select
'Wend
'k = k + l + 4 + m
'm = m + 1
'l = l + k
'MsgBox (l)
q = 0
tempint = ActiveCell.Column + 1
tempint2 = ActiveCell.Row + 1
Sheets("temp_data_2").Select
Range("A1").Select
ActiveCell.Value = tempint
Range("A2").Select
ActiveCell.Value = tempint2
Range("A3").Select
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(ADDRESS(1,R[-2]C,4),""1"","""")&R[-1]C"
temppos = ActiveCell.Value
'MsgBox (temppos)
Sheets("Summary").Select
Range(temppos).Select
temppos2 = temppos
'MsgBox (temppos2)
'firstcolnum = CInt((Range(selectedarea & 1).Column))
'special test
'special test end
Else
If ActiveCell.Value = "TOTAL" Then
Exit Sub
End If
'testing start
If q = 0 Then
Sheets("Z").Select
Range(temppos2).Select
While InStr(ActiveCell.Value, "Total") = 0
ActiveCell.Offset(1, 0).Select
Wend
k = ActiveCell.Row
'MsgBox (k)
End If
q = 1
Sheets(1).Select
Range(temppos).Select
ActiveCell.Offset(0, 19).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C40:R124C51,MATCH(RC[-19],Z!R5C3:R124C3,0)+1,FALSE),0)"
Range(temppos).Select
ActiveCell.Offset(0, 20).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R124C39,MATCH(RC[-20],Z!R5C3:R124C3,0)+1,FALSE),0)"
Range(temppos).Select
ActiveCell.Offset(0, 21).Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range(temppos).Select
Dim temp As Double
'MsgBox (temppos2)
'MsgBox (k)
Sheets("temp_data").Select
Range("A1").Select
On Error Resume Next
ActiveCell.FormulaR1C1 = _
"=MATCH(TEXT(Summary!R1C25,""mmm"")&""-""&TEXT(Summary!R1C25,""yy""),Z!R[3]C[3]:R[3]C[38],0)+3"
ActiveCell.Offset(0, 1).Range("A1").Select
On Error Resume Next
ActiveCell.FormulaR1C1 = "=COLUMN(INDIRECT(""C6""))+RC[-1]"
ActiveCell.Offset(0, 1).Range("A1").Select
On Error Resume Next
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(ADDRESS(1,RC[-2],4),""1"","""")"
ActiveCell.Offset(0, 1).Range("A1").Select
On Error Resume Next
ActiveCell.FormulaR1C1 = "=RC[-1] "
ActiveCell.Value = ActiveCell.Value & k
temp2 = ActiveCell.Value
Sheets("Z").Select
Range(temp2).Select
temp = ActiveCell.Value
Sheets("temp_data").Select
Range("E1").Select
ActiveCell.Value = temp
Sheets("Summary").Select
Range(temppos).Select
ActiveCell.Offset(0, 22).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R124C39,MATCH(RC[-22],Z!R5C3:R124C3,0)+1,FALSE),0)/ " & temp & " ,0)"
Range(temppos).Select
ActiveCell.Offset(0, 23).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R124C39,MATCH(RC[-23],Z!R5C3:R124C3,0)+1,FALSE)/HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy"")-1,Z!R4C4:R124C39,MATCH(RC[-23],Z!R5C3:R124C3,0)+1,FALSE)-1,0)"
'ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R17C27,MATCH(RC[-3],X!R5C3:R17C3)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R17C27,MATCH(RC[-3],X!R5C3:R17C3)+1,FALSE)-1,0)"
Range(temppos).Select
ActiveCell.Offset(0, 24).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R124C39,MATCH(RC[-24],Z!R5C3:R124C3,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),X!R4C4:R124C39,MATCH(RC[-24],X!R5C3:R124C3,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Y!R4C4:R124C39,MATCH(RC[-24],Y!R5C3:R124C3,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R124C39,MATCH(RC[-24],Z!R5C3:R124C3,0)+1,FALSE),0)),0)"
'testing done
Sheets("Summary").Select
Range(temppos).Select
ActiveCell.Offset(1, 0).Select
temppos = Replace(ActiveCell.Address, "quot;, "")
End If
Wend
'MsgBox (ActiveCell.Address)
End Sub
Sub removeproblem()
Sheets("Summary").Select
Range("B38").Select
If ActiveCell.Value = "Total Orissa State" Or ActiveCell.Value = "" Then
ActiveCell.EntireRow.Delete
Else
End If
Sheets("X").Select
Range("B38").Select
If ActiveCell.Value = "Total Orissa State" Or ActiveCell.Value = "" Then
ActiveCell.EntireRow.Delete
Else
End If
Sheets("Y").Select
Range("B38").Select
If ActiveCell.Value = "Total Orissa State" Or ActiveCell.Value = "" Then
ActiveCell.EntireRow.Delete
Else
End If
Sheets("Z").Select
Range("B38").Select
If ActiveCell.Value = "Total Orissa State" Or ActiveCell.Value = "" Then
ActiveCell.EntireRow.Delete
Else
End If
End Sub
Sub addproblem()
Sheets("Summary").Select
Range("B38").Select
If ActiveCell.Value <> "Total Orissa State" Then
Rows("38:38").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
Sheets("X").Select
Range("B38").Select
If ActiveCell.Value <> "Total Orissa State" Then
Rows("38:38").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
Sheets("Y").Select
Range("B38").Select
If ActiveCell.Value <> "Total Orissa State" Then
Rows("38:38").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
Sheets("Z").Select
Range("B38").Select
If ActiveCell.Value <> "Total Orissa State" Then
Rows("38:38").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
Sheets("summary").Select
End Sub
Sub finaltouch()
Application.DisplayAlerts = False
Sheets("Summary").Select
Range("D18").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
Range("E18").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
Range("F18").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("G18").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-5],X!R5C2:R125C2,0)+1,FALSE),0)/ 66958,0)"
Range("H18").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-6],X!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R125C39,MATCH(RC[-6],X!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("I18").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-7],X!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-7],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R125C39,MATCH(RC[-7],Y!R5C2:R125C2,0)+1,False),0)+IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R125C39,MATCH(RC[-7],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("J18").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
Range("K18").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
Range("L18").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
Range("M18").Select
cal_after_dark ("B18")
Range("P18").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
Range("Q18").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
Range("R18").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("S18").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-17],Y!R5C2:R125C2,0)+1,FALSE),0)/ 65469,0)"
Range("T18").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-18],Y!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy"")-1,Y!R4C4:R125C39,MATCH(RC[-18],Y!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("U18").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-19],Y!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),X!R4C4:R125C39,MATCH(RC[-19],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-19],Y!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Z!R4C4:R125C39,MATCH(RC[-19],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("V18").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
Range("W18").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
Range("X18").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("Y18").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-23],Z!R5C2:R125C2,0)+1,FALSE),0)/ 88723,0)"
Range("Z18").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-24],Z!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy"")-1,Z!R4C4:R125C39,MATCH(RC[-24],Z!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("AA18").Select
ActiveCell.FormulaR1C1 = "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-25],Z!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),X!R4C4:R125C39,MATCH(RC[-25],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Y!R4C4:R125C39,MATCH(RC[-25],Y!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-25],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("Y18").Select
Range("D37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
Range("E37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
Range("F37").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("G37").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-5],X!R5C2:R125C2,0)+1,FALSE),0)/77466,0)"
Range("H37").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-6],X!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R125C39,MATCH(RC[-6],X!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("I37").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-7],X!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-7],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R125C39,MATCH(RC[-7],Y!R5C2:R125C2,0)+1,False),0)+IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R125C39,MATCH(RC[-7],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("J37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
Range("K37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
Range("L37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
Range("M37").Select
cal_after_dark ("B37")
Range("P37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
Range("Q37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
Range("R37").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("S37").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-17],Y!R5C2:R125C2,0)+1,FALSE),0)/92339,0)"
Range("T37").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-18],Y!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy"")-1,Y!R4C4:R125C39,MATCH(RC[-18],Y!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("U37").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-19],Y!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),X!R4C4:R125C39,MATCH(RC[-19],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-19],Y!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Z!R4C4:R125C39,MATCH(RC[-19],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("V37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
Range("W37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
Range("X37").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("Y37").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-23],Z!R5C2:R125C2,0)+1,FALSE),0)/ 89993,0)"
Range("Z37").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-24],Z!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy"")-1,Z!R4C4:R125C39,MATCH(RC[-24],Z!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("AA37").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-25],Z!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),X!R4C4:R125C39,MATCH(RC[-25],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Y!R4C4:R125C39,MATCH(RC[-25],Y!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-25],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("AA38").Select
Range("D59").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
Range("E59").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
Range("F59").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("G59").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-5],X!R5C2:R125C2,0)+1,FALSE),0)/ 105079,0)"
Range("H59").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-6],X!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R125C39,MATCH(RC[-6],X!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("I59").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-7],X!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-7],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R125C39,MATCH(RC[-7],Y!R5C2:R125C2,0)+1,False),0)+IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R125C39,MATCH(RC[-7],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("J59").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
Range("K59").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
Range("L59").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
Range("L59").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
Range("M59").Select
cal_after_dark ("B59")
Range("P59").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
Range("Q59").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
Range("R59").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("S59").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-17],Y!R5C2:R125C2,0)+1,FALSE),0)/117369,0)"
Range("T59").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-18],Y!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy"")-1,Y!R4C4:R125C39,MATCH(RC[-18],Y!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("U59").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-19],Y!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),X!R4C4:R125C39,MATCH(RC[-19],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-19],Y!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Z!R4C4:R125C39,MATCH(RC[-19],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("V59").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
Range("W59").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
Range("X59").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("Y59").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-23],Z!R5C2:R125C2,0)+1,FALSE),0)/90089,0)"
Range("Z59").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-24],Z!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy"")-1,Z!R4C4:R125C39,MATCH(RC[-24],Z!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("AA59").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-25],Z!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),X!R4C4:R125C39,MATCH(RC[-25],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Y!R4C4:R125C39,MATCH(RC[-25],Y!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-25],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("AA60").Select
Range("D98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
Range("E98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
Range("F98").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("G98").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-5],X!R5C2:R125C2,0)+1,FALSE),0)/ 186763,0)"
Range("H98").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-6],X!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R125C39,MATCH(RC[-6],X!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("I98").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-7],X!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-7],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R125C39,MATCH(RC[-7],Y!R5C2:R125C2,0)+1,False),0)+IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R125C39,MATCH(RC[-7],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("J98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
Range("K98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
Range("L98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
Range("M98").Select
cal_after_dark ("B98")
Range("P98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
Range("Q98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
Range("R98").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("S98").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-17],Y!R5C2:R125C2,0)+1,FALSE),0)/192944,0)"
Range("T98").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-18],Y!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy"")-1,Y!R4C4:R125C39,MATCH(RC[-18],Y!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("U98").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-19],Y!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),X!R4C4:R125C39,MATCH(RC[-19],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-19],Y!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Z!R4C4:R125C39,MATCH(RC[-19],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("V98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
Range("W98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
Range("X98").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("Y98").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-23],Z!R5C2:R125C2,0)+1,FALSE),0)/213253,0)"
Range("Z98").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-24],Z!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy"")-1,Z!R4C4:R125C39,MATCH(RC[-24],Z!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("AA98").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-25],Z!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),X!R4C4:R125C39,MATCH(RC[-25],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Y!R4C4:R125C39,MATCH(RC[-25],Y!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-25],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("AA99").Select
Range("D123").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
Range("E123").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
Range("F123").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("G123").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-5],X!R5C2:R125C2,0)+1,FALSE),0)/116589,0)"
Range("H123").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-6],X!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R125C39,MATCH(RC[-6],X!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("I123").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-7],X!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-7],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R125C39,MATCH(RC[-7],Y!R5C2:R125C2,0)+1,False),0)+IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R125C39,MATCH(RC[-7],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("I123").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-7],X!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-7],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R125C39,MATCH(RC[-7],Y!R5C2:R125C2,0)+1,False),0)+IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R125C39,MATCH(RC[-7],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("J123").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
Range("K123").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
Range("L123").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
Range("M123").Select
cal_after_dark ("B123")
Range("P123").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
Range("Q123").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
Range("R123").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("S123").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-17],Y!R5C2:R125C2,0)+1,FALSE),0)/129051,0)"
Range("T123").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-18],Y!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy"")-1,Y!R4C4:R125C39,MATCH(RC[-18],Y!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("U123").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-19],Y!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),X!R4C4:R125C39,MATCH(RC[-19],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-19],Y!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Z!R4C4:R125C39,MATCH(RC[-19],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("V123").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
Range("W123").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
Range("X123").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("Y123").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-23],Z!R5C2:R125C2,0)+1,FALSE),0)/128749,0)"
Range("Z123").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-24],Z!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy"")-1,Z!R4C4:R125C39,MATCH(RC[-24],Z!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("AA123").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-25],Z!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),X!R4C4:R125C39,MATCH(RC[-25],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Y!R4C4:R125C39,MATCH(RC[-25],Y!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-25],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("AA124").Select
Range("B18:AA18").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("B37:AA37").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("B59:AA59").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("B98:AA98").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("B123:AA123").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("B123:AA123").Select
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("D124").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C"
Range("E124").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C"
Range("F124").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-4],X!R5C2:R125C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C40:R125C51,MATCH(RC[-4],X!R5C2:R125C2,0)+1,FALSE),0),0)"
Range("H124").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-6],X!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R125C39,MATCH(RC[-6],X!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("I124").Select
ActiveCell.FormulaR1C1 = "=(R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C)/5"
Range("P124").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C"
Range("Q124").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C"
Range("R124").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-16],Y!R5C2:R125C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C40:R125C51,MATCH(RC[-16],Y!R5C2:R125C2,0)+1,FALSE),0),0)"
Range("T124").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-18],Y!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy"")-1,Y!R4C4:R125C39,MATCH(RC[-18],Y!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("U124").Select
ActiveCell.FormulaR1C1 = "=(R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C)/5"
Range("V124").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C"
Range("W124").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C"
Range("X124").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-22],Z!R5C2:R125C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C40:R125C51,MATCH(RC[-22],Z!R5C2:R125C2,0)+1,FALSE),0),0)"
Range("Z124").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-24],Z!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy"")-1,Z!R4C4:R125C39,MATCH(RC[-24],Z!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("AA124").Select
ActiveCell.FormulaR1C1 = "=(R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C)/5"
Range("B124:AA124").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.Font
.Name = "Calibri"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.Font
.Name = "Calibri"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Range("B3:AA124").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("B3:C3,B3:C3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ThemeColor = 6
.TintAndShade = -0.499984740745262
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ThemeColor = 6
.TintAndShade = -0.499984740745262
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ThemeColor = 6
.TintAndShade = -0.499984740745262
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ThemeColor = 6
.TintAndShade = -0.499984740745262
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ThemeColor = 6
.TintAndShade = -0.499984740745262
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ThemeColor = 6
.TintAndShade = -0.499984740745262
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ThemeColor = 6
.TintAndShade = -0.499984740745262
.Weight = xlThin
End With
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("B3:C4").Select
Selection.Style = "Accent6"
Range("B38:AA38").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.499984740745262
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Columns("B:AA").EntireColumn.AutoFit
Range("B38:AA38").EntireRow.AutoFit
Sheets("X").Select
Range("B38").Select
ActiveCell.FormulaR1C1 = "Total Orissa State"
Range("B38:C38").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("D38").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C+R[-20]C)"
Range("D38").Select
Selection.Copy
Range("E38").Select
ActiveSheet.Paste
Range("F38").Select
ActiveSheet.Paste
Range("G38").Select
ActiveSheet.Paste
Range("H38").Select
ActiveSheet.Paste
Range("I38").Select
ActiveSheet.Paste
Range("J38").Select
ActiveSheet.Paste
Range("K38").Select
ActiveSheet.Paste
Range("L38").Select
ActiveSheet.Paste
Range("M38").Select
ActiveSheet.Paste
Range("N38").Select
ActiveSheet.Paste
Range("O38").Select
ActiveSheet.Paste
Range("P38").Select
ActiveSheet.Paste
Range("Q38").Select
ActiveSheet.Paste
Range("R38").Select
ActiveSheet.Paste
Range("S38").Select
ActiveSheet.Paste
Range("T38").Select
ActiveSheet.Paste
Range("U38").Select
ActiveSheet.Paste
Range("V38").Select
ActiveSheet.Paste
Range("W38").Select
ActiveSheet.Paste
Range("X38").Select
ActiveSheet.Paste
Range("Y38").Select
ActiveSheet.Paste
Range("Z38").Select
ActiveSheet.Paste
Range("AA38").Select
ActiveSheet.Paste
Range("AB38").Select
ActiveSheet.Paste
Range("AC38").Select
ActiveSheet.Paste
Range("AD38").Select
ActiveSheet.Paste
Range("AE38").Select
ActiveSheet.Paste
Range("AF38").Select
ActiveSheet.Paste
Range("AG38").Select
ActiveSheet.Paste
Range("AH38").Select
ActiveSheet.Paste
Range("AI38").Select
ActiveSheet.Paste
Range("AJ38").Select
ActiveSheet.Paste
Range("AK38").Select
ActiveSheet.Paste
Range("AL38").Select
ActiveSheet.Paste
Range("AM38").Select
ActiveSheet.Paste
Range("AN38").Select
ActiveSheet.Paste
Range("AO38").Select
ActiveSheet.Paste
Range("AP38").Select
ActiveSheet.Paste
Range("AQ38").Select
ActiveSheet.Paste
Range("AR38").Select
ActiveSheet.Paste
Range("AS38").Select
ActiveSheet.Paste
Range("AT38").Select
ActiveSheet.Paste
Range("AU38").Select
ActiveSheet.Paste
Range("AV38").Select
ActiveSheet.Paste
Range("AW38").Select
ActiveSheet.Paste
Range("AX38").Select
ActiveSheet.Paste
Range("AY38").Select
ActiveSheet.Paste
Range("B38:AY38").Select
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.499984740745262
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Range("B38:C38").Select
Range("B38:AY38").Select
Selection.Copy
Sheets("Y").Select
Range("B38").Select
ActiveSheet.Paste
Sheets("Z").Select
Range("B38").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
Sheets("X").Select
Range("B3:AY124").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Sheets("Y").Select
Range("B3:AY124").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Sheets("Z").Select
Range("B3:AY124").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
ActiveWorkbook.Save
Sheets("Summary").Select
Range("B38:C38").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "Total Orissa State"
Range("B38:C38").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("B38:AA38").EntireRow.AutoFit
Range("D38").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C+R[-1]C)"
Range("E38").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C+R[-1]C)"
Range("F38").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("H38").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-6],X!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R125C39,MATCH(RC[-6],X!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("I38").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-7],X!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-7],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R125C39,MATCH(RC[-7],Y!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R125C39,MATCH(RC[-7],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("J38").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C+R[-1]C)"
Range("K38").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C+R[-1]C)"
Range("L38").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C+R[-1]C)"
Range("M38").Select
cal_after_dark ("B38")
Range("P38").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C+R[-1]C)"
Range("Q38").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C+R[-1]C)"
Range("R38").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("T38").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-18],Y!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy"")-1,Y!R4C4:R125C39,MATCH(RC[-18],Y!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("U38").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-19],Y!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),X!R4C4:R125C39,MATCH(RC[-19],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Y!R4C4:R125C39,MATCH(RC[-19],Y!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C18,""mmm"")&""-""&TEXT(R1C18,""yy""),Z!R4C4:R125C39,MATCH(RC[-19],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("V38").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C+R[-1]C)"
Range("W38").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C+R[-1]C)"
Range("X38").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("Z38").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-24],Z!R5C2:R125C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy"")-1,Z!R4C4:R125C39,MATCH(RC[-24],Z!R5C2:R125C2,0)+1,FALSE)-1,0)"
Range("AA38").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-25],Z!R5C2:R125C2,0)+1,FALSE),0) / (IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),X!R4C4:R125C39,MATCH(RC[-25],X!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Y!R4C4:R125C39,MATCH(RC[-25],Y!R5C2:R125C2,0)+1,FALSE),0)+IFERROR(HLOOKUP(TEXT(R1C25,""mmm"")&""-""&TEXT(R1C25,""yy""),Z!R4C4:R125C39,MATCH(RC[-25],Z!R5C2:R125C2,0)+1,FALSE),0)),0)"
Range("B38:AA38").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.499984740745262
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Range("B37:AA37").Select
Selection.Font.Bold = False
Selection.Font.Bold = True
Range("B38:AA38").Select
Selection.Font.Bold = False
Selection.Font.Bold = True
Range("B18:AA18").Select
Selection.Font.Bold = False
Selection.Font.Bold = True
Range("B3:AA4").Select
Selection.Font.Bold = True
Range("F5:F125").Select
Selection.Style = "Percent"
Range("G5:G125").Select
Selection.Style = "Percent"
Range("H5:H125").Select
Selection.Style = "Percent"
Range("I5:I125").Select
Selection.Style = "Percent"
Range("M5:M125").Select
Selection.Style = "Percent"
Range("N5:N125").Select
Selection.Style = "Percent"
Range("O5:O125").Select
Selection.Style = "Percent"
Range("R5:R125").Select
Selection.Style = "Percent"
Range("S5:S125").Select
Selection.Style = "Percent"
Range("T5:T125").Select
Selection.Style = "Percent"
Range("U5:U125").Select
Selection.Style = "Percent"
Range("X5:X125").Select
Selection.Style = "Percent"
Range("Y5:Y125").Select
Selection.Style = "Percent"
Range("Z5:Z125").Select
Selection.Style = "Percent"
Range("AA5:AA125").Select
Selection.Style = "Percent"
Range("J124").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C)"
Range("J124").Select
Selection.Copy
Range("K124").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("L124").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Columns("L:L").EntireColumn.AutoFit
Columns("K:K").EntireColumn.AutoFit
cal_after_dark ("B124")
Range("B3").Select
End Sub
Sub cal_after_dark(temppos As String)
'MsgBox (temppos)
'L3M& calculation in total
' Dim temppos As String
Dim result1 As Long
Dim result2 As Long
result1 = 0
result2 = 0
Dim str2 As String
'Dim str2 As String
Sheets("Summary").Select
Range(temppos).Select
str2 = """" & ActiveCell.Value & """"
'MsgBox (str2)
For i = 0 To 2
Range(temppos).Select
ActiveCell.Offset(0, 10).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )
result1 = result1 + CLng(ActiveCell.Value)
Next i
Range(temppos).Select
ActiveCell.Offset(0, 10).Select
ActiveCell.Value = result1
For i = 0 To 2
Range(temppos).Select
ActiveCell.Offset(0, 10).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )
result2 = result2 + CLng(ActiveCell.Value)
' MsgBox (result2)
Next i
Range(temppos).Select
ActiveCell.Offset(0, 10).Select
' MsgBox (result1 & " " & result2)
On Error Resume Next
ActiveCell.Value = result1 / result2 - 1
ActiveCell.NumberFormat = "0%"
'cal done
'ytd% cal for total
result1 = 0
result2 = 0
Range(temppos).Select
ActiveCell.Offset(0, 11).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH(RC[-12],X!R5C2:R125C2,0)+1,FALSE),0),0)"
result1 = CLng(ActiveCell.Value)
ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R125C39,MATCH(RC[-12],X!R5C2:R125C2,0)+1,FALSE),0),0)"
result2 = CLng(ActiveCell.Value)
On Error Resume Next
ActiveCell.Value = result1 / result2 - 1
ActiveCell.NumberFormat = "0%"
'cal done
'ytd% for total
Range(temppos).Select
ActiveCell.Offset(0, 12).Select
result1 = 0
result2 = 0
Sheets("Summary").Select
Range(temppos).Select
str2 = """" & ActiveCell.Value & """"
For i = 0 To 11
Range(temppos).Select
ActiveCell.Offset(0, 12).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )
' MsgBox (ActiveCell.Value)
result1 = result1 + CLng(ActiveCell.Value)
Next i
'MsgBox (result1)
For i = 0 To 11
Range(temppos).Select
ActiveCell.Offset(0, 12).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )
' MsgBox (ActiveCell.Value)
result2 = result2 + CLng(ActiveCell.Value)
Next i
' MsgBox (result2)
Range(temppos).Select
ActiveCell.Offset(0, 12).Select
On Error Resume Next
ActiveCell.Value = result1 / result2 - 1
ActiveCell.NumberFormat = "0%"
result1 = 0
result2 = 0
'cal done
End Sub
No comments:
Post a Comment