Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Formula for current month minus one = Quarter number in a macro. | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Macro Formula revision? | Excel Worksheet Functions |