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?

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?
>
>

Update: I came to find that if I set the Calculation Options to "Manual" at
the start of my code and then set it back to "Automatic" at the end of my
code then my UDF worked correctly. Also, rather than using the autofill
method, I just added the "=jonathan(myrange)" formula to the entire range of
cells that I wanted the formula to be in. The "myrange" ranges changed as
needed thanks to relative referencing.

"Anant Basant" wrote:

> 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?
> >
> >