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
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
