set formula by Macro
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 |
set formula by Macro
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 |
set formula by Macro
.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 |
set formula by Macro
Sorry my aim is off, change to & not $
"John Bundy" wrote: 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 |
All times are GMT +1. The time now is 08:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com