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