Filter VBA Project,Sourav Bhattacharya,Excel VBA faculty,Kolkata

Please send me a message to view the file,it's copyrighted
Google sheet does not support this,so you have to download this and see in excel to understand it.


Sourav Bhattacharya
Excel VBA Faculty

Final automated summary report after ytd and ytd growth calculation added

Three subs are added

 
Sub ytdcal(temppos As String)

Dim exampleDate As Date

Dim i As Integer


Dim str2 As String
Dim result As Long
result = 0

 Range(temppos).Select
str2 = """" & ActiveCell.Value & """"
Sheets("Summary").Select


If InStr((Range("$I$1").Value), "16") <> 0 Then
'MsgBox ("true")
exampleDate = DateValue(Range("$I$1").Value)
result = 0

For i = 4 To Month(exampleDate)

Range(temppos).Select
    ActiveCell.Offset(0, 8).Select

 ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT( " & Month(exampleDate) + (4 - i) & " *28,""mmm"") & ""-"" & TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH( " & str2 & ",X!R5C3:R125C3,0)+1,FALSE),0)"
        result = result + CLng(ActiveCell.Value)
       
        Next i

'MsgBox (result)
 Range(temppos).Select
    ActiveCell.Offset(0, 8).Select
        ActiveCell.Value = result

'MsgBox (result)


Else
result = 0
exampleDate = DateValue(Range("$I$1").Value)

For i = 0 To (8 + Month(exampleDate))
Range(temppos).Select
    ActiveCell.Offset(0, 8).Select
  ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )       
        result = result + CLng(ActiveCell.Value)
       
        Next i
        Range(temppos).Select
    ActiveCell.Offset(0, 8).Select
        ActiveCell.Value = result
       
      '  MsgBox (result)
End If




End Sub



Sub ytdcom(temppos As String)

Dim exampleDate As Date

Dim i As Integer


Dim str2 As String
Dim result1 As Long
Dim result2 As Long

result1 = 0
result2 = 0


 Range(temppos).Select
str2 = """" & ActiveCell.Value & """"
Sheets("Summary").Select
Range(temppos).Select
    ActiveCell.Offset(0, 8).Select
    result1 = ActiveCell.Value
   


'MsgBox ("true")
exampleDate = DateValue(Range("$I$1").Value)

'MsgBox (Year(exampleDate))

If (Year(exampleDate) - 1) = 2015 Then

exampleDate = DateValue(Range("$I$1").Value)
result = 0

For i = 4 To Month(exampleDate)

Range(temppos).Select
    ActiveCell.Offset(0, 11).Select

 ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT( " & Month(exampleDate) + (4 - i) & " *28,""mmm"") & ""-"" & TEXT(R1C9,""yy"")-1,X!R4C4:R125C39,MATCH( " & str2 & ",X!R5C3:R125C3,0)+1,FALSE),0)"
        result2 = result2 + CLng(ActiveCell.Value)
       
        Next i
Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
        ActiveCell.Value = result2

Else
exampleDate = DateValue(Range("$I$1").Value)

For i = 0 To (8 + Month(exampleDate))
Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
  ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )       
        result2 = result2 + CLng(ActiveCell.Value)
       
        Next i
        Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
        ActiveCell.Value = result2
       

End If
 Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
    On Error Resume Next
   
 ActiveCell.Value = (result1 / result2) - 1


      '  MsgBox (result)





End Sub



Sub ytdcomtotal(temppos As String)

Dim exampleDate As Date

Dim i As Integer


Dim str2 As String
Dim result1 As Long
Dim result2 As Long

result1 = 0
result2 = 0


 Range(temppos).Select
str2 = """" & ActiveCell.Value & """"
Sheets("Summary").Select
Range(temppos).Select
    ActiveCell.Offset(0, 8).Select
    result1 = ActiveCell.Value
   


'MsgBox ("true")
exampleDate = DateValue(Range("$I$1").Value)

'MsgBox (Year(exampleDate))

If (Year(exampleDate) - 1) = 2015 Then

exampleDate = DateValue(Range("$I$1").Value)
result = 0

For i = 4 To Month(exampleDate)

Range(temppos).Select
    ActiveCell.Offset(0, 11).Select

 ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT( " & Month(exampleDate) + (4 - i) & " *28,""mmm"") & ""-"" & TEXT(R1C9,""yy"")-1,X!R4C4:R125C39,MATCH( " & str2 & ",X!R5C2:R125C2,0)+1,FALSE),0)"
        result2 = result2 + CLng(ActiveCell.Value)
       
        Next i
Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
        ActiveCell.Value = result2

Else
exampleDate = DateValue(Range("$I$1").Value)

For i = 0 To (8 + Month(exampleDate))
Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
  ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )       
        result2 = result2 + CLng(ActiveCell.Value)
       
        Next i
        Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
        ActiveCell.Value = result2
       

End If
 Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
    On Error Resume Next
   
 ActiveCell.Value = (result1 / result2) - 1


      '  MsgBox (result)





End Sub









Sourav Bhattacharya
Excel Vba Teacher 
919748184075

Use a variable value from cell in Countif,Sourav Bhattacharya,Excel Faculty(91-9748184075)

The formula was

=COUNTIFS(INDIRECT("'"&$A$2&"'!$A$3:$A$1048576"),'Dlr wise Tier Performance(K)'!$A$4,INDIRECT("'"&$A$2&"'!$E$3:$E$1048576"),'Dlr wise Tier Performance(K)'!$B$4,INDIRECT("'"&$A$2&"'!M3:M1048576"),"<>")

where A2 cell has the changing value

See the file

Sourav Bhattacharya

Advanced Excel Faculty

Kolkata

91-9748184075

Interesting date calculation formula for my student

So I have a issue like this

Jan-15	12
Feb-15	12
Mar-15	12
Apr-15	12
May-15	11
Jun-15	10
Jul-15	9
Aug-15	8
Sep-15	7
Oct-15	6
Nov-15	5
Dec-15	4
Jan-16	3
Feb-16	2
Mar-16	1
Apr-16	0
May-16	0

The left side data will be coming from the formula ,example if an employee has the joining on Jan 15 the formula will give his employment time as 12 month



The reference date for this is 4/1/2016
which is in N2







On G2 I have a date 3/24/2015(I have month/date/year format)


On I2 I should write this

=IF(YEAR(G2)=YEAR($N$2),MONTH($N$2)-MONTH(G2),(YEAR($N$2)-YEAR(G2))*12+(MONTH($N$2)-MONTH(G2)))

and on H2 I have to write this


=IF(I2>=12,12,IF(I2>=11,11,IF(I2>=10,10,IF(I2>=9,9,IF(I2>=8,8,IF(I2>=7,7,IF(I2>=6,6,IF(I2>=5,5,IF(I2>=4,4,IF(I2>=3,3,IF(I2>=2,2,IF(I2>=1,1,IF(I2>=0,0,IF(I2>=-1,0))))))))))))))












voila problem solved







I have added the excel file







but then the student said the formula to wrapped in one column

so

the formula ended up like this


=IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=12,12,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=11,11,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=10,10,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=9,9,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=8,8,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=7,7,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=6,6,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=5,5,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=4,4,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=3,3,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=2,2,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=1,1,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=0,0,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=-1,0,0))))))))))))))





This is the final file


Sourav Bhattacharya
Excel/Excel VBA Teacher 
Mail me at allsourav2atgmaildotcom

Final Summary Report Automation Full Source Code,VBA Teacher Sourav

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