Use formula in cell loop

[sharky12345]

How can I use a cell address in a formula within a range loop?

I've tried the below but it doesn't work;

For Each cell In Range("B3:B18")
If cell.Value <> 0 Then
cell.Offset(0, 1).Formula = "=IF(Cell="""","""",COUNTIF(DEPLOYMENTS!$S$2:$S10000,Cell))"
End If
Next

 

[JoeMo]

Untested.

Cell.Offset(0, 1).Formula = "=IF(" & Cell.Value = "" & ","""",COUNTIF(DEPLOYMENTS!$S$2:$S10000," & Cell.Value & "))"

 

[sharky12345]

Thanks Joe - doesn't work I'm afraid, I get 'FALSE' as a result.

 

[MARK858]

What is the working formula in C3 if B3 is populated and you typed it in manually?

 

[sharky12345]

This:

=IF(B3="","",COUNTIF(DEPLOYMENTS!$S$2:$S10007,B3))

 

[Fluff]

Try

cell.Offset(0, 1).Formula = "=IF(" & cell.Address & "= """" ,"""",COUNTIF(pcode!$S$2:$S10000," & cell.Address & "))"

 

[sharky12345]

Fluff,

Thanks, that works, though the 'pcode' part threw me slightly!

Thank you to all that replied.

 

[Fluff]

Glad we could help & thanks for the feedback.