Run-Time Error 1004 : The Text you have entered is not a Valid reference or defined name

Question

Alrighty So this seams like a really simple line of Code...

 

I am using Excel 2007

 

I have narrowed my error down to this silly line of code.  The Error i am getting is 1004: The text you have entered is not a valid reference or defined name...

 

Sheets("Items").Range("ItemNumberHeader").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= (Range("AI1").CurrentRegion), Unique:=False

 

(This is all on a single line No need for the Continuation _)

 

Here is my Complete Subroutine for Reference.

 

Sub FindAction()
' This Action Finds the Product Entered into the Query Page / Items Page for Ordering

    Dim strDesckeywords As String
    Dim lngWords As Long
    Dim arrIn() As String
    Dim i As Integer
    Dim rng As Range
   
    Application.ScreenUpdating = False
   
    ' Copies User Entered Item Number for Searching (Query)
    Sheets("Query").Select
    ActiveSheet.Range("Item_Num").Select
    Selection.Copy
    Sheets("Items").Select
    Application.Goto Reference:="ItemFind"
    ActiveSheet.Paste
    Sheets("Items").Range("DescriptionCriteriaArea").ClearContents
    strDesckeywords = Sheets("Query").Range("Description_Keywords").Value
    lngWords = WordsToArray_TSB(strDesckeywords, arrIn())
    For i = 1 To lngWords Step 1
        Debug.Print "*" & arrIn(i) & "*"
        Sheets("Items").Range("DescriptionFind").Offset(i - 1, 0) = "*" & arrIn(i) & "*"
    Next i

   

    'Preforms Search Functions
    'Basic (works But Limited Abilities)
    'Sheets("Items").Range("ItemNumberHeader").CurrentRegion.AutoFilter Field:=2, Criteria1:="=*Black*"

    'Advanced Search Functionality (NOT WORKING)
    Sheets("Items").Range("ItemNumberHeader").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=(Range("AI1").CurrentRegion), Unique:=False

 
    'Selects Top Cell for Ease Of End user Usage
    Application.Goto Reference:="ItemNumberHeader"

End Sub

Answer 1

Ah Haa..

 

I found the Evil Doers..

 

Here is the working Code.

 

Sheets("Items").Range("ItemNumberHeader").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=(Sheets("Items").Range("AI1").CurrentRegion), Unique:=False