autofilter macro causes #VALUE! error

[Guest]

Using Excel 2003..

I created a simple macro command button... Autofilter - Custom - equals "Open" OR equals "Active

When I autofilter with the same criteria, things work fine. If I use the autofilter macro, it causes #VALUE! errors in several cells.

I have a UDF...

Function isformula(rng As Range

Application.Volatile (True
isformula = rng.HasFormul

End Functio

An example of a formula causing the error: =IF(K55="","",IF(AND(isformula(K55),K55=TODAY()),"",TODAY())

It seems related to recalculate and the UDF... b/c once I press F9, the errors are gone. But I can't figure out how to force recalculation within the macro. (I don't want users to have to press F9) I've tried application.calculate, and other suggestions found in this news group, but nothing's worked so far

Any suggestions?

TIA
Jill.

 

[Ed]

Try "Worksheets("SheetName").Calculate.

HTH
Ed

Using Excel 2003...

I created a simple macro command button... Autofilter - Custom - equals "Open" OR equals "Active"

When I autofilter with the same criteria, things work fine. If I use the

autofilter macro, it causes #VALUE! errors in several cells.

I have a UDF...

Function isformula(rng As Range)

Application.Volatile (True)
isformula = rng.HasFormula

End Function

An example of a formula causing the error: =IF(K55="","",IF(AND(isformula(K55),K55=TODAY()),"",TODAY()))

It seems related to recalculate and the UDF... b/c once I press F9, the

errors are gone. But I can't figure out how to force recalculation within
the macro. (I don't want users to have to press F9) I've tried
application.calculate, and other suggestions found in this news group, but
nothing's worked so far.

 

[Guest]

still doesn't seem to be working...

----- Ed wrote: ----

Try "Worksheets("SheetName").Calculate

HT
E

Using Excel 2003..

errors are gone. But I can't figure out how to force recalculation withi
the macro. (I don't want users to have to press F9) I've trie
application.calculate, and other suggestions found in this news group, bu
nothing's worked so far

 

[Ed]

Sorry, Jill. I have a macro in which I use
' Update calculations in tables
wb2.Worksheets("Sheet1").Calculate
wb2.Worksheets("Sheet2").Calculate
wb2.Worksheets("Sheet3").Calculate
with no problems (wb2 has been declared and set). If these don't do it,
then maybe the issue is not with Calculate. If so, then it's probably 'way
beyond me.

Ed