vba Range("B8").Formula = "=COUNTIF(B2:J2,B7)"

[montecarlo2012]

Hello.
I have this formula

Range("B8").Formula = "=COUNTIF(B2:J2,B7)"

I am trying to use Cells Method like:

Cells(8, 2).formula = “=countif(“ & cells(2, 2) & “” : “” & cells(20, 2) & “, & cells(7, 2)”

but no results.

Please can you explain me why, and how to do it.
or it is not possible. I don't know,

thanks for reading.

 

[Marc L]

Hi,​

​

you are confusing cell content and cell address …​

 

[Michael M]

Use

Cells(8, 2).Formula = "=countif(" & Cells(2, 2).Address & ":" & Cells(20, 2).Address & "," & Cells(7, 2) & ")"

 

[GWteB]

... meaning, Cells default property is Value
When any property is omitted, the default property is implicitly obtained. You're in need of addresses, so you have to specify this explicitly. The following might point you in the right direction.

Sub montecarlo2012()

    Dim AbsRng As String, RelRng As String

    AbsRng = Cells(2, 2).Address & ":" & Cells(20, 2).Address & "," & Cells(7, 2).Address
    RelRng = Replace(AbsRng, "$", "")

    Cells(8, 2).Formula = "=COUNTIF(" & RelRng & ")"

End Sub

 

[Michael M]

Also, a bit cleaner

[B8].Formula = "=countif(" & [b2].Address & ":" & [J20].Address & "," & [B7] & ")"

 

[montecarlo2012]

Michael M Thank you I marked as solution your post. Great lesson.

 

[montecarlo2012]

Hello Marc, Can you explain me please my confusion
Thanks for your respond.

 

[montecarlo2012]

GWteB Thank you so much Sir.​

Very Kind of you to explain me, and thank you for the code

 

[GWteB]

You are welcome and thanks for letting us know.

 

[Marc L]

Hello Marc, Can you explain me please my confusion

As the cell content can't be the cell address …​