ExcelBanter - Countif in VBA

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Countif in VBA (https://www.excelbanter.com/excel-worksheet-functions/69001-countif-vba.html)

hi, i have this countif formula that i need it to run thru all the rows
of data..

=COUNTIF(A1:A3772,"<"&A*) where * range from 1 to 1000

i wrote the following vba code but it give an 'application defined or
object defined error'

Code:
--------------------

Sub fill()
Dim irow, lastrow As Integer
Dim frmla As String

lastrow = ActiveSheet.UsedRange.Rows.Count
For irow = 1 To lastrow
frmla = "=COUNTIF(A1:A" & lastrow & ",<&A" & irow & ")"
ActiveSheet.Cells(irow, 4).Formula = frmla
Next irow

End Sub

--------------------

anyone knows where went wrong?

--
xxbenxx
------------------------------------------------------------------------
xxbenxx's Profile: http://www.excelforum.com/member.php...o&userid=30884
View this thread: http://www.excelforum.com/showthread...hreadid=507587

You have to double up those quotes in your strings:

frmla = "=COUNTIF(A1:A" & lastrow & ",""<""&A" & irow & ")"

xxbenxx wrote:

hi, i have this countif formula that i need it to run thru all the rows
of data..

=COUNTIF(A1:A3772,"<"&A*) where * range from 1 to 1000

i wrote the following vba code but it give an 'application defined or
object defined error'

Code:
--------------------

Sub fill()
Dim irow, lastrow As Integer
Dim frmla As String

lastrow = ActiveSheet.UsedRange.Rows.Count
For irow = 1 To lastrow
frmla = "=COUNTIF(A1:A" & lastrow & ",<&A" & irow & ")"
ActiveSheet.Cells(irow, 4).Formula = frmla
Next irow

End Sub

--------------------

anyone knows where went wrong?

--
xxbenxx
------------------------------------------------------------------------
xxbenxx's Profile: http://www.excelforum.com/member.php...o&userid=30884
View this thread: http://www.excelforum.com/showthread...hreadid=507587

--

Dave Peterson