Syntax of formula

[forNiloy]

Hi,

I want to set formula.I am trying like this

For i = intFrom To intTo

If ActiveSheet.Range(strModule & i).Text <> "" Then

ActiveSheet.Range(strFrom & i).Formula =
"=SUM(IF(C17:C2000 = """ & ActiveSheet.Range(strModule & i).Text &
""",IF(J17:J2000 = "Fail" ,1,0)))"

Else

ActiveSheet.Range(strFrom & i).Value = 0

End If

Next i

I am getting error while I am using IF(J17:J2000 = "Fail",1,0) .What
would be the exact syntax.

And the other thing in this way I want get the sum of Fail where date
is like
{=SUM(IF(M17:M2000 = "12/2/2006",IF(J17:J2000 = "Fail",1,0),0))}
It also does not give the perfect result.

Plz.. help me in this matter

Regards,
Nil

 

[Bob Phillips]

For i = intFrom To intTo

If ActiveSheet.Range(strModule & i).Text <> "" Then

ActiveSheet.Range(strFrom & i).Formula = _
"=SUMPRODUCT(--(C17:C2000=""" & ActiveSheet.Range(strModule &
i).Text & _
"""),--(J17:J2000=""Fail""))"
Else
ActiveSheet.Range(strFrom & i).Value = 0
End If
Next i

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[forNiloy]

For i = intFrom To intTo

If ActiveSheet.Range(strModule & i).Text <> "" Then

ActiveSheet.Range(strFrom & i).Formula = _
"=SUMPRODUCT(--(C17:C2000=""" & ActiveSheet.Range(strModule &
i).Text & _
"""),--(J17:J2000=""Fail""))"
Else
ActiveSheet.Range(strFrom & i).Value = 0
End If
Next i

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

Thanks Bob,It works perfectly

Regards,
Nil