.Range("J" & i).Formula = "=COUNTIF(D17:D2000,""" & _
.Range("I" & i).Text & """)"



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"John Bundy" wrote in message
...
Just a little tweak, you were trying to add a number 'i' to letters of
your
range e.g.(AI+21)
Change the '+' to '$' and your good.

" wrote:

Hi,

I want to set formula -COUNTIF() in cell.I am trying to do this like --

--------------------------------------------------------------------------
------------------------
Private Sub Copyformula_Click()

Dim i As Integer

If Not Range("AJ21 : AJ85").HasFormula Then

For i = 21 To 85

With ActiveSheet

.Range("AJ" + i).Cells.Formula = "=COUNTIF(D17:D2000,"
& Range("AI" + i).Text & ")"


End With

Next i

End If

End Sub

--------------------------------------------------------------------------
-----------------------------
I am getting error as "Type mismatch". Here I am exactly trying to
implement the logic to assign the formula in each cell.The formula will
count the presence of the text of that range.

Plz.. help me with the correct code.

Thanks and Regards,
Nil