Thanks for your input. My code fragment may not have been clear. That
FillAcctCode function works correctly when the IsFiltered function is not
referenced in any cell. Here's the whole code section:

For Each rngCurrRow In rngCatSubcat
If Target.Row = Range("Bud_ExpenditureTable").Row Then
colCategory = Range("Bud_CatSubCatCols").Column
colAccount = Range("Bud_AcctCodeCol").Column

' If the character in the override column isn't the same as char in
header, then fill in the account code.
If ActiveSheet.Cells(rngCurrRow.Row, colAccount - 1).Value < _
ActiveSheet.Cells(Range("Bud_AcctCodeCol").Row - 1, colAccount -
1).Value Then

' If the Category or Subcategory is empty then clear the account code,
' else fill in account code and put space in override column.
If Cells(rngCurrRow.Row, colCategory) = "" Or Cells(rngCurrRow.Row,
colCategory + 1) = "" Then
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = ""
Else
' Put a char in override colum so Item Description doesn't bleed
into column.
ActiveSheet.Cells(rngCurrRow.Row, colAccount - 1).Value = " "

' If we got a valid account code, fill in account code column.
strAcctCode = BuildAccountCode(rngCurrRow)
If Len(Trim(strAcctCode)) 0 Then
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value =
strAcctCode
Else
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = ""
End If
End If

Else
' The Override is set, but check if the user has set category and
subcategory
' that matches the account code.
Set rngCurrAcct = Range(Cells(rngCurrRow.Row, colAccount),
Cells(rngCurrRow.Row, colAccount))
SetOverRide rngCurrAcct
End If

Else
colCategory = Range("Bud_AllocationTable").Column + 1
colAccount = Range("Bud_AllocationTable").Column
strAcctCode = BuildAccountCode(rngCurrRow)

If Len(Trim(strAcctCode)) 0 Then
' Fails at next line.
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = strAcctCode
Else
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = ""
End If
End If
Next rngCurrRow

It seems that as soon as I put the IsFiltered function in any cell formula,
then this line following line in the FillAcctCode generates the 1004 error:

ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = strAcctCode

Of course this code may be fine but some other condition in Excel is being
set that causes it to fail. Just for grins sake I put the following formula
in an otherwise blank unused cell. And I still got the failure.
=IsFiltered(range("$c$30:$Q$159"))

The IsFiltered() function seems to be doing what it's suppose to do. I'm
going to tear apart the IsFiltered function to see if I can narrow down the
trigger.

Any feedback is appreciated.

John.

"Patrick Molloy" wrote:

in here
Sub FillAcctCode(ByVal Target As Range)

near where you marked the error you haev a second ELSE statement

your syntax should be either
IF .... ELSE ... END IF
or
IF .... ELSEIF [.....ELSEIF] ...[ELSE]...END IF

you can't have more than one ELSE



"DocBrown" wrote:

I hope someone can redirect me before my head gets too bloody from beating it
against the wall.

I have a UDF that's intended to determine if an AutoFilter is active on a
portion of my worksheet. I got the basic idea from another posting here. This
UDF is referenced in cell in another portion of the WS. The intention is to
blank a cell if an AutoFilter is On.

I have another Macro invoked by the Worksheet_Change event that will try to
fill in cells in rows based on the current selected cells and other lists in
another worksheet.

The error is 1004 - Application-defined or object-defined error. It occurs
when I modify one of the cells that invokes function below called
FillAcctCode().

The weird part is that the failure only occurs if two workbooks are opened
at the same time. The two workbooks have most of the same macros. (one is a
modified version of the other where I'm trying to get this AutoFilter stuff
working.)

Trying to avoid putting too much code here, here's what I think are the
relevant parts:

The cell macro:
=IF(AND(NotFiltered($C$29:$Q$159),OR(F13<"",G13 < ""),H13< ""),H13-I13,"")

The UDF:

Private Function NotFiltered(MyRange As Range) As String
Application.Volatile
Dim i As Integer
NotFiltered = True
With MyRange.Parent.AutoFilter
If Intersect(MyRange, .Range) Is Nothing Then Exit Function
For i = 1 To .Range.Columns.Count
With .Filters(i)
If .On Then
NotFiltered = False
End If
End With
Next
End With

End Function


The other macro code that hits the error:

Sub FillAcctCode(ByVal Target As Range)
'================================================= =====================
' By: John Spitzer
' Date: 05/15/2009

Dim rngCatSubcat As Range
Dim rngCurrRow As Range
Dim rngCurrAcct As Range
Dim colCategory As Long
Dim colAccount As Long
Dim strAcctCode As String

On Error GoTo ErrThisSub
If Target.Row = Range("Bud_ExpenditureTable").Row Then
Set rngCatSubcat = Application.Intersect(Target,
Range("Bud_CatSubCatCols"))
Else
Set rngCatSubcat = Application.Intersect(Target,
Range("Bud_AllocationTable"))
End If
' Loop through all the selected rows and
' if the category or subcategory columns are empty clear the account code
' else fill in the account code.
For Each rngCurrRow In rngCatSubcat
If Target.Row = Range("Bud_ExpenditureTable").Row Then

' process rows in the main table.

else
colCategory = Range("Bud_AllocationTable").Column + 1
colAccount = Range("Bud_AllocationTable").Column
strAcctCode = BuildAccountCode(rngCurrRow)
If Len(Trim(strAcctCode)) 0 Then
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = strAcctCode
'<-- fails here
Else
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = "" '<-- or
fails here
End If
End If
Next rngCurrRow

Does anyone have an idea where to look for the problem?

Thanks a bunch!
Johin