Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF interaction with other macro causes Error 1004
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|