Using Cell.Value within formula

[sharky12345]

I'm trying to use 'Cell.Value' in a formula but it isn't working - it's putting the phrase itself in rather than the cell reference, this is what I have;

For Each Cell In Range("U2:U20")
If Cell <> "" Then
Cell.Offset(0, 1).Formula = "=COUNTIF(Deployments!$S$2:$S5000,Cell)"
End If
Next

Can someone show me where I'm going wrong please?

 

[wideboydixon]

Depends if you want your formula to reference the cell or the cell value. If you want to use the address of the cell you can do this:

        Cell.Offset(0, 1).Formula = "=COUNTIF(Deployments!$S$2:$S5000," & Cell.Address & ")"

If the cell contains a numeric value then you could do this:

        Cell.Offset(0, 1).Formula = "=COUNTIF(Deployments!$S$2:$S5000," & Cell.Value & ")"

Finally, if the cell contains a string value then you could do this:

        Cell.Offset(0, 1).Formula = "=COUNTIF(Deployments!$S$2:$S5000,""" & Cell.Value & """)"

WBD

 

[sharky12345]

Perfect - thanks for the detailed explanation, gives me a number of options!