Tuesday, May 31, 2016

Professional Summary Sheet Creation code Excel VBA,Sourav Bhattacharya

Sub final()

Application.ScreenUpdating = False
Application.EnableEvents = False

    Sheets("Summary").Select
    Sheets("Summary").Name = "Sheet1"
Sheets("Sheet1").Select
Range("B38").Select
If ActiveCell.Value = "Total Orissa State" Then
ActiveCell.EntireRow.Delete
Else
End If



'
'X

On Error Resume Next
test7

On Error Resume Next
test8
On Error Resume Next
test9


Sheets("Sheet1").Select

'
Range("D18").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R123C27,MATCH(RC[-2],X!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C28:R123C39,MATCH(RC[-2],X!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R123C27,MATCH(RC[-4],X!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R123C27,MATCH(RC[-4],X!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R123C27,MATCH(RC[-8],Z!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C28:R123C39,MATCH(RC[-8],Z!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R123C27,MATCH(RC[-10],Z!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Z!R4C4:R123C27,MATCH(RC[-10],Z!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R123C27,MATCH(RC[-14],Y!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C28:R123C39,MATCH(RC[-14],Y!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R123C27,MATCH(RC[-16],Y!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Y!R4C4:R123C27,MATCH(RC[-16],Y!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
    ActiveCell.Offset(1, 0).Range("A1").Select
 
    ActiveCell.Offset(18, -17).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R123C27,MATCH(RC[-2],X!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C28:R123C39,MATCH(RC[-2],X!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R123C27,MATCH(RC[-4],X!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R123C27,MATCH(RC[-4],X!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R123C27,MATCH(RC[-8],Z!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C28:R123C39,MATCH(RC[-8],Z!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    ActiveCell.Offset(0, 3).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R123C27,MATCH(RC[-8],Z!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C28:R123C39,MATCH(RC[-8],Z!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R123C27,MATCH(RC[-10],Z!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Z!R4C4:R123C27,MATCH(RC[-10],Z!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Offset(0, -2).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R123C27,MATCH(RC[-8],Z!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C28:R123C39,MATCH(RC[-8],Z!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, 3).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R123C27,MATCH(RC[-14],Y!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C28:R123C39,MATCH(RC[-14],Y!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R123C27,MATCH(RC[-16],Y!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Y!R4C4:R123C27,MATCH(RC[-16],Y!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    ActiveCell.Offset(22, -17).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R123C27,MATCH(RC[-2],X!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C28:R123C39,MATCH(RC[-2],X!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R123C27,MATCH(RC[-4],X!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R123C27,MATCH(RC[-4],X!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R123C27,MATCH(RC[-8],Z!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C28:R123C39,MATCH(RC[-8],Z!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R123C27,MATCH(RC[-10],Z!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Z!R4C4:R123C27,MATCH(RC[-10],Z!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R123C27,MATCH(RC[-14],Y!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C28:R123C39,MATCH(RC[-14],Y!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R123C27,MATCH(RC[-16],Y!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Y!R4C4:R123C27,MATCH(RC[-16],Y!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
    ActiveCell.Offset(1, 0).Range("A1").Select
  
    ActiveCell.Offset(38, -17).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R123C27,MATCH(RC[-2],X!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C28:R123C39,MATCH(RC[-2],X!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R123C27,MATCH(RC[-4],X!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R123C27,MATCH(RC[-4],X!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R123C27,MATCH(RC[-8],Z!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C28:R123C39,MATCH(RC[-8],Z!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R123C27,MATCH(RC[-10],Z!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Z!R4C4:R123C27,MATCH(RC[-10],Z!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R123C27,MATCH(RC[-14],Y!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C28:R123C39,MATCH(RC[-14],Y!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R123C27,MATCH(RC[-16],Y!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Y!R4C4:R123C27,MATCH(RC[-16],Y!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
    ActiveCell.Offset(25, -17).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R123C27,MATCH(RC[-2],X!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C28:R123C39,MATCH(RC[-2],X!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R123C27,MATCH(RC[-4],X!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R123C27,MATCH(RC[-4],X!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R123C27,MATCH(RC[-8],Z!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C28:R123C39,MATCH(RC[-8],Z!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R123C27,MATCH(RC[-10],Z!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Z!R4C4:R123C27,MATCH(RC[-10],Z!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R123C27,MATCH(RC[-14],Y!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C28:R123C39,MATCH(RC[-14],Y!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R123C27,MATCH(RC[-16],Y!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Y!R4C4:R123C27,MATCH(RC[-16],Y!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Offset(0, -5).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R123C27,MATCH(RC[-14],Y!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C28:R123C39,MATCH(RC[-14],Y!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R123C27,MATCH(RC[-14],Y!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C28:R123C39,MATCH(RC[-14],Y!R5C2:R123C2,0)+1,FALSE),0),0)"
    ActiveCell.Offset(-25, 0).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(25, 0).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R123C27,MATCH(RC[-16],Y!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Y!R4C4:R123C27,MATCH(RC[-16],Y!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R123C27,MATCH(RC[-16],Y!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Y!R4C4:R123C27,MATCH(RC[-16],Y!R5C2:R123C2,0)+1,FALSE)-1,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    ActiveCell.Offset(1, 0).Range("A1").Select

    ActiveWorkbook.Save
  
  
    Sheets("Sheet1").Select

'
    Range("B18:U18").Select
  
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    Range("B37:U37").Select
  
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    Range("B59:U59").Select

    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
  
    Range("B98:U98").Select
  
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("B123:U123").Select
  
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With



 Sheets("Sheet1").Select
    Range("D5:F123").Select
  
'ActiveCell.EntireColumn.Select

    Selection.Style = "Percent"
Range("J5:L123").Select
    Selection.Style = "Percent"
  Range("P5:R123").Select
    Selection.Style = "Percent"
  
  
  
    Sheets("Sheet1").Select
    Range("B38").Select
    If ActiveCell.Value <> "Total Orissa State" Then
    Rows("38:38").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
  
    End If
  
  
    Sheets("Sheet1").Select
    Range("B38:C38").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("B38:C38").Select
    ActiveCell.FormulaR1C1 = "Total Orissa State"
    Range("B38").Select
    ActiveWorkbook.Save
  
    'added for total orissa and grand total
  
     Sheets("Sheet1").Select
    Range("D38").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R123C27,MATCH(RC[-2],X!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C28:R123C39,MATCH(RC[-2],X!R5C2:R123C2,0)+1,FALSE),0),0)"
    Range("E38").Select
    ActiveCell.FormulaR1C1 = "=(R[-1]C+R[-20]C)/2"
    Range("F38").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R123C27,MATCH(RC[-4],X!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R123C27,MATCH(RC[-4],X!R5C2:R123C2,0)+1,FALSE)-1,0)"
    Range("G38").Select
    ActiveCell.FormulaR1C1 = "=R[-20]C+R[-1]C"
    Range("H38").Select
    ActiveCell.FormulaR1C1 = "=R[-20]C+R[-1]C"
    Range("I38").Select
    ActiveCell.FormulaR1C1 = "=R[-20]C+R[-1]C"
    Range("J38").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R123C27,MATCH(RC[-8],Z!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C28:R123C39,MATCH(RC[-8],Z!R5C2:R123C2,0)+1,FALSE),0),0)"
    Range("K38").Select
    ActiveCell.FormulaR1C1 = "=(R[-1]C+R[-20]C)/2"
    Range("L38").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R123C27,MATCH(RC[-10],Z!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Z!R4C4:R123C27,MATCH(RC[-10],Z!R5C2:R123C2,0)+1,FALSE)-1,0)"
    Range("M38").Select
    ActiveCell.FormulaR1C1 = "=R[-20]C+R[-1]C"
    Range("N38").Select
    ActiveCell.FormulaR1C1 = "=R[-20]C+R[-1]C"
    Range("O38").Select
    ActiveCell.FormulaR1C1 = "=R[-20]C+R[-1]C"
    Range("P38").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R123C27,MATCH(RC[-14],Y!R5C2:R123C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C28:R123C39,MATCH(RC[-14],Y!R5C2:R123C2,0)+1,FALSE),0),0)"
    Range("Q38").Select
    ActiveCell.FormulaR1C1 = "=(R[-1]C+R[-20]C)/2"
    Range("R38").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R123C27,MATCH(RC[-16],Y!R5C2:R123C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Y!R4C4:R123C27,MATCH(RC[-16],Y!R5C2:R123C2,0)+1,FALSE)-1,0)"
    Range("S38").Select
    ActiveCell.FormulaR1C1 = "=R[-20]C+R[-1]C"
    Range("T38").Select
    ActiveCell.FormulaR1C1 = "=R[-20]C+R[-1]C"
    Range("U38").Select
    ActiveCell.FormulaR1C1 = "=R[-20]C+R[-1]C"
    Range("U39").Select
  
    Range("D124").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R124C27,MATCH(""TOTAL X"",X!R5C2:R124C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C28:R124C39,MATCH(""TOTAL X"",X!R5C2:R124C2,0)+1,FALSE),0),0)"
    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(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R124C27,MATCH(""TOTAL X"",X!R5C2:R124C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R124C27,MATCH(""TOTAL X"",X!R5C2:R124C2,0)+1,FALSE)-1,0)"
    Range("G124").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C"
    Range("H124").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C"
    Range("I124").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C"
    Range("J124").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R124C27,MATCH(""TOTAL Z"",Z!R5C2:R124C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C28:R124C39,MATCH(""TOTAL Z"",Z!R5C2:R124C2,0)+1,FALSE),0),0)"
    Range("K124").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C"
    Range("L124").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R124C27,MATCH(RC[-10],Z!R5C2:R124C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Z!R4C4:R124C27,MATCH(RC[-10],Z!R5C2:R124C2,0)+1,FALSE)-1,0)"
    Range("M124").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C"
    Range("N124").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C"
    Range("O124").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C"
    Range("P124").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R124C27,MATCH(""TOTAL Y"",Y!R5C2:R124C2,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C28:R124C39,MATCH(""TOTAL Y"",Y!R5C2:R124C2,0)+1,FALSE),0),0)"
    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(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R124C27,MATCH(""TOTAL Y"",Y!R5C2:R124C2,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Y!R4C4:R124C27,MATCH(""TOTAL Y"",Y!R5C2:R124C2,0)+1,FALSE)-1,0)"
    Range("S124").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C"
    Range("T124").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C"
    Range("U124").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+R[-26]C+R[-65]C+R[-87]C+R[-106]C"
 
 
  
 
    Range("B38:U38").Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
 
    Range("B124:U124").Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
      'added for total orissa and grand total
    
    
      'Final Touch
    
        Range("B60:U60").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
 
    Range("B99:U99").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    Range("B3:U124").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:U4").Select
    Selection.Font.Bold = False
    Selection.Font.Bold = True
    Range("B18:U18").Select
    Selection.Font.Bold = True
 
    Range("B37:U37").Select
    Selection.Font.Bold = True
    Range("B38:U38").Select
    Selection.Font.Bold = True
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With

    Range("B59:U59").Select
    Selection.Font.Bold = True
 
    Range("B98:U98").Select
    Selection.Font.Bold = True
    Range("B123:U124").Select
    Selection.Font.Bold = True

    Range("B3:U4").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    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
    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:U4").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("B3:U4").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
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    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:U4").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
  
    Sheets("Sheet1").Select
    Range("B3:U4").Select
    With Selection.Font
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.799981688894314
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.599993896298105
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.799981688894314
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
  
    Sheets("Summary").Select
    Range("B3:U4").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
  
        Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Summary"
  
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub




Sub test7()
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"

Sheets("Sheet1").Select
temppos = "C5"
temppos2 = temppos


Range(temppos).Select
While InStr(ActiveCell.Value, "Z") = 0

If InStr(ActiveCell.Value, "Total") <> 0 Then
'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("Sheet1").Select
Range(temppos).Select
temppos2 = temppos
'MsgBox (temppos2)

'firstcolnum = CInt((Range(selectedarea & 1).Column))
'special test







'special test end


Else

'testing start

If q = 0 Then

Sheets("X").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("Sheet1").Select
Range(temppos).Select

ActiveCell.Offset(0, 1).Select

 ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R122C27,MATCH(RC[-1],X!R5C3:R122C3,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C28:R122C39,MATCH(RC[-1],X!R5C3:R122C3,0)+1,FALSE),0),0)"
      
Dim temp As Double

'MsgBox (temppos2)



'MsgBox (k)





Sheets("temp_data").Select

   Range("A1").Select
   On Error Resume Next
 
    ActiveCell.FormulaR1C1 = _
        "=MATCH(TEXT(Sheet1!R1C9,""mmm"")&""-""&TEXT(Sheet1!R1C9,""yy""),X!R[3]C[3]:R[3]C[26],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("Sheet1").Select
  Range(temppos).Select

 ActiveCell.Offset(0, 2).Select

   ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R122C27,MATCH(RC[-2],X!R5C3:R122C3,0)+1,FALSE),0)/ " & temp & " ,0)"
  Range(temppos).Select

 ActiveCell.Offset(0, 3).Select
 ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R122C27,MATCH(RC[-3],X!R5C3:R122C3,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,X!R4C4:R122C27,MATCH(RC[-3],X!R5C3:R122C3,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)"
      
        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, 4).Select
  
   ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )      
        result = result + CInt(ActiveCell.Value)
      
        Next i
      Range(temppos).Select
    ActiveCell.Offset(0, 4).Select
    ActiveCell.Value = result
  
    Range(temppos).Select
    ActiveCell.Offset(0, 5).Select
  
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),X!R4C4:R122C27,MATCH(RC[-5],X!R5C3:R122C3,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, 6).Select
   ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )      
        result = result + CInt(ActiveCell.Value)
      
        Next i
      
     Range(temppos).Select
    ActiveCell.Offset(0, 6).Select
    ActiveCell.Value = result








'end action







'testing done
Range(temppos).Select


ActiveCell.Offset(1, 0).Select

temppos = Replace(ActiveCell.Address, "$", "")


End If




Wend
'MsgBox (ActiveCell.Address)



End Sub





Sub test8()
'New






'End New






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"

Sheets("Sheet1").Select
temppos = "C5"
temppos2 = temppos


Range(temppos).Select
While InStr(ActiveCell.Value, "Z") = 0

If InStr(ActiveCell.Value, "Total") <> 0 And InStr(ActiveCell.Value, "Orissa") = 0 Then

'Sheets("Z").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)

'New Part


'End New
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("Sheet1").Select
Range(temppos).Select
temppos2 = temppos
'MsgBox (temppos2)

'firstcolnum = CInt((Range(selectedarea & 1).Column))
'special test







'special test end


Else

'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("Sheet1").Select
Range(temppos).Select

ActiveCell.Offset(0, 7).Select

 ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R122C27,MATCH(RC[-7],Z!R5C3:R122C3,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C28:R122C39,MATCH(RC[-7],Z!R5C3:R122C3,0)+1,FALSE),0),0)"
      
Dim temp As Double

'MsgBox (temppos2)



'MsgBox (k)





Sheets("temp_data").Select

   Range("A1").Select
   On Error Resume Next
 
    ActiveCell.FormulaR1C1 = _
        "=MATCH(TEXT(Sheet1!R1C9,""mmm"")&""-""&TEXT(Sheet1!R1C9,""yy""),Z!R[3]C[3]:R[3]C[26],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("Sheet1").Select
  Range(temppos).Select

 ActiveCell.Offset(0, 8).Select

   ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R122C27,MATCH(RC[-8],Z!R5C3:R122C3,0)+1,FALSE),0)/ " & temp & " ,0)"
  Range(temppos).Select

 ActiveCell.Offset(0, 9).Select
 ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R122C27,MATCH(RC[-9],Z!R5C3:R122C3,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Z!R4C4:R122C27,MATCH(RC[-9],Z!R5C3:R122C3,0)+1,FALSE)-1,0)"
 'ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R17C27,MATCH(RC[-3],Z!R5C3:R17C3)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Z!R4C4:R17C27,MATCH(RC[-3],Z!R5C3:R17C3)+1,FALSE)-1,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, 10).Select
  
   ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )      
        result = result + CInt(ActiveCell.Value)
      
        Next i
      Range(temppos).Select
    ActiveCell.Offset(0, 10).Select
    ActiveCell.Value = result
  
    Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
  
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Z!R4C4:R122C27,MATCH(RC[-11],Z!R5C3:R122C3,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, 12).Select
   ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )      
        result = result + CInt(ActiveCell.Value)
      
        Next i
      
     Range(temppos).Select
    ActiveCell.Offset(0, 12).Select
    ActiveCell.Value = result








'end action







'testing done
Range(temppos).Select


ActiveCell.Offset(1, 0).Select

temppos = Replace(ActiveCell.Address, "$", "")


End If




Wend
'MsgBox (ActiveCell.Address)









End Sub





Sub test9()

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"

Sheets("Sheet1").Select
temppos = "C5"
temppos2 = temppos


Range(temppos).Select
While InStr(ActiveCell.Value, "Z") = 0

If InStr(ActiveCell.Value, "Total") <> 0 Then
'Sheets("Y").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("Sheet1").Select
Range(temppos).Select
temppos2 = temppos
'MsgBox (temppos2)

'firstcolnum = CInt((Range(selectedarea & 1).Column))
'special test







'special test end


Else

'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("Sheet1").Select
Range(temppos).Select

ActiveCell.Offset(0, 13).Select

 ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R122C27,MATCH(RC[-13],Y!R5C3:R122C3,0)+1,FALSE),0)/IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C28:R122C39,MATCH(RC[-13],Y!R5C3:R122C3,0)+1,FALSE),0),0)"
      
Dim temp As Double

'MsgBox (temppos2)



'MsgBox (k)





Sheets("temp_data").Select

   Range("A1").Select
   On Error Resume Next
 
    ActiveCell.FormulaR1C1 = _
        "=MATCH(TEXT(Sheet1!R1C9,""mmm"")&""-""&TEXT(Sheet1!R1C9,""yy""),Y!R[3]C[3]:R[3]C[26],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("Sheet1").Select
  Range(temppos).Select

 ActiveCell.Offset(0, 14).Select

   ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R122C27,MATCH(RC[-14],Y!R5C3:R122C3,0)+1,FALSE),0)/ " & temp & " ,0)"
  Range(temppos).Select

 ActiveCell.Offset(0, 15).Select
 ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R122C27,MATCH(RC[-15],Y!R5C3:R122C3,0)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Y!R4C4:R122C27,MATCH(RC[-15],Y!R5C3:R122C3,0)+1,FALSE)-1,0)"
 'ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R17C27,MATCH(RC[-3],Y!R5C3:R17C3)+1,FALSE)/HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy"")-1,Y!R4C4:R17C27,MATCH(RC[-3],Y!R5C3:R17C3)+1,FALSE)-1,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, 16).Select
  
   ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )      
        result = result + CInt(ActiveCell.Value)
      
        Next i
      Range(temppos).Select
    ActiveCell.Offset(0, 16).Select
    ActiveCell.Value = result
  
    Range(temppos).Select
    ActiveCell.Offset(0, 17).Select
  
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(HLOOKUP(TEXT(R1C9,""mmm"")&""-""&TEXT(R1C9,""yy""),Y!R4C4:R122C27,MATCH(RC[-17],Y!R5C3:R122C3,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, 18).Select
   ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )      
        result = result + CInt(ActiveCell.Value)
      
        Next i
      
     Range(temppos).Select
    ActiveCell.Offset(0, 18).Select
    ActiveCell.Value = result








'end action







'testing done
Range(temppos).Select


ActiveCell.Offset(1, 0).Select

temppos = Replace(ActiveCell.Address, "$", "")


End If




Wend
'MsgBox (ActiveCell.Address)









End Sub











No comments:

Post a Comment