A UDF can not change anything on the worksheet, for example it can not format
cells etc. That is the limitation of a user defined function and not of the
AutoFill property.



"Jonathan Brown" wrote:

I've created a function that will count the number of cells in a range that
don't contain a formula or are numeric. I named this function =Jonathan()
(pretty original eh?)

I then have a macro that will find my range (this part I've got figured out)
and then add the formula =jonathan(myrange) to the activecell. It then uses
the autofill method to fill the cells to the right about 50 something
columns. For some reason, all the cells besides the activecell are returning
#Value! errors. However, if I do the autofill manually with my mouse after
the macro is finished processing, the function works fine. So I know the
function works and the ranges are correct.

Now, if I replace the formula =jonathan(myrange) in the macro with the Excel
built-in function =COUNTA(myrange) then the autofill works fine, granted, I
don't get the results I want but the autofill works correctly.

Is there some limitation to using autofill in VBA with a User Defined
Function? Do I need to add something to my UDF to tell autofill how to treat
it?