Sub KumarPK()
Dim rcell As Range
Application.ScreenUpdating = False
Range("D5:D" & Range("A" & Rows.Count).End(3).Row).Replace "", "NULL"
For Each rcell In Range("C5:D" & Range("A" & Rows.Count).End(3).Row)
If rcell.Value <> "NULL" Then
rcell.Value = CDate(rcell)
End If
Next rcell
For Each rcell In Range("H5:I" & Range("A" & Rows.Count).End(3).Row)
rcell.Value = CDate(rcell)
Next rcell
For Each rcell In Range("BR5:BS" & Range("A" & Rows.Count).End(3).Row)
rcell.Value = CDate(rcell)
Next rcell
Range("A5:BY" & Range("A" & Rows.Count).End(3).Row).SpecialCells(4).Value = "NULL"
Range("BZ5:BZ" & Range("A" & Rows.Count).End(3).Row).Formula = "=IF(BT5=AI5,""CURRENT MONTH"",""CATCHUP/CATCHDOWN"")"
Range("CA5:CA" & Range("A" & Rows.Count).End(3).Row).Formula = "=IF(CB5<0,0,IF(CB5>1,1,CB5))"
Range("CB5:CB" & Range("A" & Rows.Count).End(3).Row).Formula = "=IF(ISERROR((AL5/CD5)*MIN(CF5,CG5,CE5)),0,((AL5/CD5)*MIN(CF5,CG5,CE5)))"
Range("CC5:CC" & Range("A" & Rows.Count).End(3).Row).Formula = "=IF(BZ5=""CURRENT MONTH"",IF(AL5<0,0,AL5),0)"
Range("CD5:CD" & Range("A" & Rows.Count).End(3).Row).Formula = "=IF(CC5=0,0,SUMIF(A:A,A5,CC:CC))"
Range("CE5:CE" & Range("A" & Rows.Count).End(3).Row).Formula = "=IF(BZ5=""CURRENT MONTH"",IF(C5<$CG$1,100%,IF(ISERROR((D5-C5)/($CG$2-$CG$1)),100%,((D5-C5)/($CG$2-$CG$1)))),0)"
Range("CF5:CF" & Range("A" & Rows.Count).End(3).Row).Formula = "=IF(BZ5=""CURRENT MONTH"",IF(C5<$CG$1,100%,($CG$2-C5)/($CG$2-$CG$1)),0)"
Range("CG5:CG" & Range("A" & Rows.Count).End(3).Row).Formula = "=IF(BZ5=""CURRENT MONTH"",IF(D5=""NULL"",100%,IF(D5>$CG$2,100%,(D5-$CG$1)/($CG$2-$CG$1))),0)"
Range("CH5:CH" & Range("A" & Rows.Count).End(3).Row).Formula = "=IF(BZ5=""CURRENT MONTH"",IF(OR(AO5=""NULL"",AO5=100633,AO5=104857,AO5=100634),0,AL5),0)"
Range("CI5:CI" & Range("A" & Rows.Count).End(3).Row).Formula = "=IF(BZ5=""CURRENT MONTH"",AK5,0)"
Range("CJ5:CJ" & Range("A" & Rows.Count).End(3).Row).Formula = "=IF(BZ5=""CURRENT MONTH"",SUMIF(A:A,A5,CH:CH),0)"
Range("CK5:CK" & Range("A" & Rows.Count).End(3).Row).Formula = "=IF(BZ5=""CURRENT MONTH"",SUMIF(A:A,A5,CI:CI),0)"
Range("CL5:CL" & Range("A" & Rows.Count).End(3).Row).Formula = "=IF(OR(CH5=0,AV5=""company"",BM5=""Level 9"",BM5=""Level 10"",BM5=""Level 11"",CJ5<80),""-"",IF(CK5=0,""Buffer"",""-""))"
Range("CM5:CM" & Range("A" & Rows.Count).End(3).Row).Formula = "=IF(CL5=""Buffer"",CA5,0)"
Range("CN5:CN" & Range("A" & Rows.Count).End(3).Row).Formula = "=IF(OR(BM5=""level 1"",BM5=""level 2""),CA5,0)"
With Range("BZ5:CN" & Range("A" & Rows.Count).End(3).Row)
.Value = .Value
End With
Application.ScreenUpdating = True
End Sub
Bookmarks