Just random mumbling: 2016

Monday, December 12, 2016

Unable to lock the administration directory /var/log/dpkg/,is another process using it,solved

sudo lsof /var/lib/dpkg/lock

Find the process PID which is using the lock

kill -9 (PID that you found using lsof)


source:http://askubuntu.com/questions/15433/unable-to-lock-the-administration-directory-var-lib-dpkg-is-another-process

Thursday, October 27, 2016

Microsoft.Reporting.WinForms.ReportViewer is not defined or The referenced component 'Microsoft.ReportViewer.Common' could not be found issue solved

I downloaded Microsoft Report Viewer 2010 Redistributable Package for dot net 4.0 or visual studio 2010 and installed it, but the problem persists

So from references I removed Microsoft.ReportViewer.Common and Microsoft.ReportViewer.WinForms.

and then I added the two references by going to add reference and then going to in the .net reference section.

and the issue solved.

Friday, October 14, 2016

VBA Macro to find next workday after variable number of days considering old indian style week off(alternate saturday off and fixed sunday off),VBA Teacher Sourav,Kolkata 09748184075

Sub test()
Sheets(1).Select

Dim exampleDate As Date
Dim initiandate As Date
Dim enddate As Date

exampleDate = DateValue("10/5/2016")
initialdate = DateValue("10/5/2016")
'exampleDate = exampleDate + 1
'MsgBox (Day(exampleDate))
'MsgBox WeekdayName(3, True, vbMonday)
'Dim val As String
' Range("B1").Select
' ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""ddd"")"
' val = ActiveCell.Value
' MsgBox WeekdayName(Weekday(exampleDate), True, vbSunday)
Dim result As Integer
result = 0
Dim i, j As Integer
j = 21
i = 1
Dim check As Integer
check = 0

While i <= j
If WeekdayName(Weekday(exampleDate), True, vbSunday) = "Sun" Or WeekdayName(Weekday(exampleDate), True, vbSunday) = "Sat" Then
'MsgBox ("Sun")
check = 1
j = j + 1
i = i + 1
GoTo lastline
End If
exampleDate = exampleDate + 1
i = i + 1
'MsgBox (result)
lastline:
If check = 1 Then
exampleDate = exampleDate + 1
check = 0
End If

'MsgBox (exampleDate)
Wend

'MsgBox (exampleDate)
'MsgBox (exampleDate)
enddate = exampleDate

Dim sampledate As Date

Dim sampledatestr As String
sampledatestr = (CStr(Month(initialdate)) + "/" + CStr(1) + "/" + CStr(Year(initialdate)))
'MsgBox (sampledatestr)
sampledate = DateValue(sampledatestr)
Dim tempdate As Date
tempdate = sampledate
'MsgBox (tempdate)
'MsgBox (enddate)
'MsgBox (sampledate)
check = 0
For sampledate = tempdate To enddate
If WeekdayName(Weekday(sampledate), True, vbSunday) = "Sat" Then
If check = 1 Then
exampleDate = exampleDate - 1
check = 0

End If

End If
check = 1

Next sampledate

MsgBox (exampleDate)

End Sub

Saturday, August 13, 2016

JAVA_HOME\bin not working solved windows 8

windows+R and type sysdm.cpl

go to advanced

create a new variable(click new)

variable name JAVA_HOME

variable value C:\Program Files\Java\jdk1.8.0_102

then

on below section scroll down and find path

select path

click edit

put a semicolon at the end

then write

%JAVA_HOME%\bin

click ok and exit

then open command prompt with administrative rights

setx JAVA_HOME "C:\Program Files\Java\jdk1.8.0_102"



if the command succeed,the issue solved

source:http://superuser.com/questions/79612/setting-and-getting-windows-environment-variables-from-the-command-prompt

Monday, August 1, 2016

Second ListBox project ,Filter multiple table using one ListBox,VBA Teacher Sourav Bhattacharya,Kolkata

Sub setuplistbox()

Application.ScreenUpdating = False
Application.EnableEvents = False
Workbooks(ActiveWorkbook.Name).Activate
ActiveWorkbook.Sheets(2).Activate

reset


   ' Range("AJ3").Select
   ' Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

    'Range("AI3").Select
' Range(Selection, Selection.End(xlDown)).Select
   ' Selection.Copy
    'Range("AJ3").Select
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'Application.CutCopyMode = False
    'Range("AJ3").Select
    Dim i As Integer

    'Dim myrange As Range
    'Range(Selection, Selection.End(xlDown)).Select
    'Set myrange = Selection
    'MsgBox (myrange.Address)

    'ActiveSheet.Range(myrange.Address).RemoveDuplicates Columns:=1, Header:=xlNo

    'Range("AJ3").Select
    'Range(Selection, Selection.End(xlDown)).Select
    Dim options(6) As String
   ' ReDim options(Selection.Rows.count) As String
    ' Dim cell As Object

    'For Each cell In Selection
     '   options(count) = cell.Value
      ' count = count + 1

    'Next cell

options(1) = "ODC"
options(2) = "ODW"
options(3) = "OD"
options(4) = "WB"
options(5) = "BHR"
options(6) = "JHK"


    'MsgBox Selection.Rows.count
   ' For count = 1 To UBound(options)
    'MsgBox (options(count))
   ' Next count



   ' ActiveCell.EntireColumn.Delete
   ' ActiveWorkbook.Sheets("2nd Macro").Activate
'For i = 1 To Sheets(2).ListBox1.ListCount

               'Remove an item from the ListBox.
   '           Sheets(2).ListBox1.RemoveItem 0

    '       Next i



   ' Columns("B:B").Select
   ' Selection.NumberFormat = "@"

    'Range("B6").Select
    For i = 1 To UBound(options)
Sheets(2).ListBox1.AddItem options(i)

Next i

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Sub dashcontrol()

Application.ScreenUpdating = False
Application.EnableEvents = False

Dim count1 As Integer
count1 = 0

Sheets(2).Select
    Columns("AQ:AQ").Select
    Selection.Clear

Range("AQ3").Select
ActiveCell.Value = "State"

Range("AQ4").Select
   For i = 0 To Sheets(2).ListBox1.ListCount - 1
        If Sheet2.ListBox1.Selected(i) = True Then
      count1 = count1 + 1


        End If
    Next i
    If count1 = 0 Then
    Exit Sub
    Else


    For i = 0 To Sheets(2).ListBox1.ListCount - 1
        If Sheet2.ListBox1.Selected(i) = True Then
       ActiveCell.Value = Sheets(2).ListBox1.List(i)
       ActiveCell.Offset(1, 0).Select

        End If
    Next i

Dim filterCriteria() As String

Dim count As Integer
count = 0

Range("AQ4").Select
While ActiveCell.Value <> ""
count = count + 1
ActiveCell.Offset(1, 0).Select

Wend
'MsgBox (count)
ReDim filterCriteria(count) As String
Range("AQ4").Select

For i = 1 To count
filterCriteria(i) = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Next i

ActiveSheet.Range("Table1").AutoFilter Field:=1, Criteria1:=filterCriteria, Operator:=xlFilterValues
ActiveSheet.Range("Table2").AutoFilter Field:=1, Criteria1:=filterCriteria, Operator:=xlFilterValues
ActiveSheet.Range("Table3").AutoFilter Field:=1, Criteria1:=filterCriteria, Operator:=xlFilterValues

ActiveSheet.Range("Table4").AutoFilter Field:=1, Criteria1:=filterCriteria, Operator:=xlFilterValues

ActiveSheet.Range("Table5").AutoFilter Field:=1, Criteria1:=filterCriteria, Operator:=xlFilterValues

ActiveSheet.Range("Table6").AutoFilter Field:=1, Criteria1:=filterCriteria, Operator:=xlFilterValues

ActiveSheet.Range("Table7").AutoFilter Field:=1, Criteria1:=filterCriteria, Operator:=xlFilterValues

ActiveSheet.Range("Table8").AutoFilter Field:=1, Criteria1:=filterCriteria, Operator:=xlFilterValues

ActiveSheet.Range("Table9").AutoFilter Field:=1, Criteria1:=filterCriteria, Operator:=xlFilterValues

ActiveSheet.Range("Table10").AutoFilter Field:=1, Criteria1:=filterCriteria, Operator:=xlFilterValues
   ActiveSheet.Range("Table11").AutoFilter Field:=1, Criteria1:=filterCriteria, Operator:=xlFilterValues

     ActiveSheet.Range("Table12").AutoFilter Field:=1, Criteria1:=filterCriteria, Operator:=xlFilterValues
   ActiveSheet.Range("Table13").AutoFilter Field:=1, Criteria1:=filterCriteria, Operator:=xlFilterValues

Columns("AQ:AQ").Select
    Selection.Clear

End If

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Sub reset()
Application.ScreenUpdating = False
Application.EnableEvents = False

Workbooks(ActiveWorkbook.Name).Activate
ActiveWorkbook.Sheets(2).Activate

    'Range("B4").Select


ActiveSheet.ListObjects("Table1").Range.AutoFilter
ActiveSheet.ListObjects("Table2").Range.AutoFilter
ActiveSheet.ListObjects("Table3").Range.AutoFilter
ActiveSheet.ListObjects("Table4").Range.AutoFilter
ActiveSheet.ListObjects("Table5").Range.AutoFilter
ActiveSheet.ListObjects("Table6").Range.AutoFilter
ActiveSheet.ListObjects("Table7").Range.AutoFilter
ActiveSheet.ListObjects("Table8").Range.AutoFilter
ActiveSheet.ListObjects("Table9").Range.AutoFilter
ActiveSheet.ListObjects("Table10").Range.AutoFilter
ActiveSheet.ListObjects("Table11").Range.AutoFilter
ActiveSheet.ListObjects("Table12").Range.AutoFilter
ActiveSheet.ListObjects("Table13").Range.AutoFilter

Columns("AQ:AQ").Select
    Selection.Clear


   Dim TheItems As Long

    If Sheets(2).ListBox1.MultiSelect = 0 Then
        TheListbox = Null
    Else
        For TheItems = 0 To Sheets(2).ListBox1.ListCount - 1
            If Sheets(2).ListBox1.Selected(TheItems) Then Sheets(2).ListBox1.Selected(TheItems) = False
        Next

    End If

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.Shapes.Range(Array("ListBox1")).Select
    ActiveSheet.Shapes("ListBox1").ScaleWidth 1.15625, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("ListBox1").ScaleHeight 1.1302085156, msoFalse, _
        msoScaleFromTopLeft
End Sub

Friday, July 29, 2016

Deselet all items in a listbox solved,Excel VBA Teacher Sourav Bhattacharya,Kolkata

Borrowed this code and it's working like a charm

Sub TestRun()

Call ListboxDeselectAllItems(MainForm.lstbxBrand_Converted)

End Sub


Public Sub ListboxDeselectAllItems(TheListBox As Object)
Dim TheItems As Variant

    If TheListBox.MultiSelect = 0 Then
        TheListBox = Null
    Else
        For Each TheItems In TheListBox.ItemsSelected
            TheListBox.Selected(TheItems) = False
        Next
    
    End If

End Sub

original source

Shrinking ListBox VBA Solved,Excel VBA Teacher Sourav,Kolkata

I set the Integral height to False, and make a ListBox much higher

http://www.excelforum.com/excel-programming-vba-macros/695828-shrinking-list-box.html

Sunday, July 17, 2016

How to watch a side-by-side 3D video in 2D in media player classic and vlc

Open the MPC player and configure the Pan and Scan Settings .

In the MPC player menu. Select View->Pan&Scan->Edit then a Pan&Scan Preset window setting will appear. Select "New" button then rename "new" entry to "3D[SBS] to 2D" and configure the Pos and Zoom . Then click "set" button and then click "Save" button.

source:http://play3d-2d.blogspot.in/2014/02/3d-sdb-to-2d-movie-in-media-player.html

for vlc player

http://www.techswatch.com/2015/03/how-to-watch-3d-sbs-side-by-side-videos-on-vlc-player.html

Over ride add on signing in firefox,integrate IDM with firefox

You can temporarily override the setting to enforce the add-on signing requirement by changing the preference xpinstall.signatures.required to false in the Firefox Configuration Editor (about:config page).

source:https://support.mozilla.org/en-US/kb/add-on-signing-in-firefox?as=u&utm_source=inproduct

Saturday, June 25, 2016

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

Saturday, June 18, 2016

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