set formula by Macro

F

forNiloy

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
 
B

Bob Phillips

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



--
HTH

Bob Phillips

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