D
DocBrown
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
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