Thanks for all the replies,

Some where else something is missing or having conflict:

Range D4 can have *, 1, 2, 3 and 100 thru 400
Range E4 can have *, G, and N

Based on the value in E4 and D4 different formulas are applied.

'==== Changes in formulas due to the following conditions:====

' TArl and TPhyLoc are the values affected.

' For E4 the following two values:

' If E4 < * then TArl = RC1&R4C5 ' (E4=G or N)
'If E4 = * then LEFT(TArl,3)=RC1

' For D4 the following values:

' If D4 = * then TPhyLoc not considered
' If D4 = 1 or 2 or 3 then LEFT(TPhyLoc,1) = R4C4 ' D4= First
Character of TPhyLoc
' If D4 = 100 thru 400 then TPhyLoc = R4C4 '
D4=TPhyLoc

'====

'Case = 1

If E4 = "*" And D4 = "*" Then

ActiveCell.Offset(0, 2).FormulaArray = _
"=SUM(IF(LEFT(TArl,3)=RC1,IF(TMonth=R4C2,IF(TMont h<=R5C2,IF(SalRef=""S"",ComFare,0),0),0),0),0)-SUM(IF(LEFT(TArl,3)=RC1,IF(TMonth=R4C2,IF(TMonth< =R5C2,IF(SalRef=""R"",ComFare,0),0),0),0),0)"

ActiveCell.Offset(0, 3).FormulaArray = _
"=SUM(IF(LEFT(TArl,3)=RC1,IF(TMonth=R4C2,IF(TMont h<=R5C2,IF(SalRef=""S"",Rev,0),0),0),0),0)-SUM(IF(LEFT(TArl,3)=RC1,IF(TMonth=R4C2,IF(TMonth< =R5C2,IF(SalRef=""R"",Rev,0),0),0),0),0)"

' Case = 2

ElseIf E4 = "*" And (D4 = 1 Or D4 = 2 Or D4 = 3) Then

ActiveCell.Offset(0, 2).FormulaArray = _
"=SUM(IF(left(TPhyLoc,1)=R4C4,IF(LEFT(TArl,3)=RC1, IF(TMonth=R4C2,IF(TMonth<=R5C2,IF(SalRef=""S"",Co mFare,0),0),0),0),0),0)-SUM(IF(LEFT(TPhyLoc,1)=R4C4,IF(LEFT(TArl,3)=RC1,IF (TMonth=R4C2,IF(TMonth<=R5C2,IF(SalRef=""R"",ComF are,0),0),0),0),0),0)"

ActiveCell.Offset(0, 3).FormulaArray = _
"=SUM(IF(left(TPhyLoc,1)=R4C4,IF(LEFT(TArl,3)=RC1, IF(TMonth=R4C2,IF(TMonth<=R5C2,IF(SalRef=""S"",Re v,0),0),0),0),0),0)-SUM(IF(LEFT(TPhyLoc,1)=R4C4,IF(LEFT(TArl,3)=RC1,IF (TMonth=R4C2,IF(TMonth<=R5C2,IF(SalRef=""R"",Rev, 0),0),0),0),0),0)"

' Case = 3

ElseIf E4 = "*" And (D4 < 1 Or D4 < 2 Or D4 < 3 Or D4 < "*") Then

ActiveCell.Offset(0, 2).FormulaArray = _
"=SUM(IF(TPhyLoc=R4C4,IF(Left(TArl,3)=RC1,IF(TMont h=R4C2,IF(TMonth<=R5C2,IF(SalRef=""S"",ComFare,0) ,0),0),0),0),0)-SUM(IF(TPhyLoc=R4C4,IF(Left(TArl,3)=RC1,IF(TMonth =R4C2,IF(TMonth<=R5C2,IF(SalRef=""R"",ComFare,0),0 ),0),0),0),0)"

ActiveCell.Offset(0, 3).FormulaArray = _
"=SUM(IF(TPhyLoc=R4C4,IF(LEFT(TArl,3)=RC1,IF(TMont h=R4C2,IF(TMonth<=R5C2,IF(SalRef=""S"",Rev,0),0), 0),0),0),0)-SUM(IF(TPhyLoc=R4C4,IF(LEFT(TArl,3)=RC1,IF(TMonth =R4C2,IF(TMonth<=R5C2,IF(SalRef=""R"",Rev,0),0),0) ,0),0),0)"

' Case = 4

ElseIf E4 < "*" And D4 = "*" Then

ActiveCell.Offset(0, 2).FormulaArray = _
"=SUM(IF(TArl=RC1&R4C5,IF(TMonth=R4C2,IF(TMonth<= R5C2,IF(SalRef=""S"",ComFare,0),0),0),0),0)-SUM(IF(TArl=RC1&R4C5,IF(TMonth=R4C2,IF(TMonth<=R5 C2,IF(SalRef=""R"",ComFare,0),0),0),0),0)"

ActiveCell.Offset(0, 3).FormulaArray = _
"=SUM(IF(TArl=RC1&R4C5,IF(TMonth=R4C2,IF(TMonth<= R5C2,IF(SalRef=""S"",Rev,0),0),0),0),0)-SUM(IF(TArl=RC1&R4C5,IF(TMonth=R4C2,IF(TMonth<=R5 C2,IF(SalRef=""R"",Rev,0),0),0),0),0)"

' Case = 5

ElseIf E4 < "*" And (D4 = 1 Or D4 = 2 Or D4 = 3) Then

ActiveCell.Offset(0, 2).FormulaArray = _
"=SUM(IF(left(TPhyLoc,1)=R4C4,IF(TArl=RC1&R4C5,IF( TMonth=R4C2,IF(TMonth<=R5C2,IF(SalRef=""S"",ComFa re,0),0),0),0),0),0)-SUM(IF(LEFT(TPhyLoc,1)=R4C4,IF(TArl=RC1&R4C5,IF(TM onth=R4C2,IF(TMonth<=R5C2,IF(SalRef=""R"",ComFare ,0),0),0),0),0),0)"

ActiveCell.Offset(0, 3).FormulaArray = _
"=SUM(IF(left(TPhyLoc,1)=R4C4,IF(TArl=RC1&R4C5,IF( TMonth=R4C2,IF(TMonth<=R5C2,IF(SalRef=""S"",Rev,0 ),0),0),0),0),0)-SUM(IF(LEFT(TPhyLoc,1)=R4C4,IF(TArl=RC1&R4C5,IF(TM onth=R4C2,IF(TMonth<=R5C2,IF(SalRef=""R"",Rev,0), 0),0),0),0),0)"


' Case = 6


ElseIf E4 < "*" And (D4 < 1 Or D4 < 2 Or D4 < 3 Or D4 < "*") Then

ActiveCell.Offset(0, 2).FormulaArray = _
"=SUM(IF(TPhyLoc=R4C4,IF(TArl=RC1&R4C5,IF(TMonth= R4C2,IF(TMonth<=R5C2,IF(SalRef=""S"",ComFare,0),0) ,0),0),0),0)-SUM(IF(TPhyLoc=R4C4,IF(TArl=RC1&R4C5,IF(TMonth=R4 C2,IF(TMonth<=R5C2,IF(SalRef=""R"",ComFare,0),0),0 ),0),0),0)"

ActiveCell.Offset(0, 3).FormulaArray = _
"=SUM(IF(TPhyLoc=R4C4,IF(TArl=RC1&R4C5,IF(TMonth= R4C2,IF(TMonth<=R5C2,IF(SalRef=""S"",Rev,0),0),0), 0),0),0)-SUM(IF(TPhyLoc=R4C4,IF(TArl=RC1&R4C5,IF(TMonth=R4 C2,IF(TMonth<=R5C2,IF(SalRef=""R"",Rev,0),0),0),0) ,0),0)"

End If

Any Help?