Using Find() with respect to a dynamically named range

[Atroxell]

I'm honestly not sure if the fact that the range I am attempting to use is dynamically named, butI thought I would at least make a mention of it...

So here is what I am trying to do:

I have a userform. On that form is a textbox that will accept a inventory ID. Next to that particular textbox is a button that I want to use for finding that inventory id (if it exists) and populating the rest of the textboxes. If it does not exist, then a simple message stating that is fine. My problem is that when I try to use a Find() on the named range I get the same error over and over--"Object Required". Only I can't see it. I know at this point I have gone a bit overboard in the attempts to make it work and confused myself, so I turn to all of you in humility...

I have tried every permutation of syntax I can think of, and looked all over the Web for the answer. I have found several answers, but I'm too bullheaded to change my approach. I want to use my named range to do the search. I can't help but to think the answer is looking right at me but I can't see the forest through the trees...

Private Sub tab1_quickFind_Click()
        Dim rFound As Range
        
        chkVal = tab1SupplierID
        Sheets("Inventory").Activate   [COLOR=#ff0000]<---this is the worksheet where my dynamic range is located. And the range is properly defined. I know I should be able to reference the range without activatingthe sheet, but I did so in an attempt to eliminate possibilities.
[/COLOR]     
       [COLOR=#ff0000]'Range("C1").Activate  <--this is from a prior attempt. Left it here anyway.

[/COLOR]                       [COLOR=#ff0000]' Here I have also used Range("INVSupplierID").Find(What:=chkVal, _
[/COLOR]    Set rFound = ActiveSheet.Range("C1:C" & Range("A65535").End(xlUp).Row).Find(What:=chkVal, _
                After:=ActiveCell, LookIn:=xlValues, LookAt:=xlValue, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Activate
        
        If Not rFound Is Nothing Then
                .
                .
                .                
        End If
End Sub

Anyway, I'm sure if the answer were a dog it would be biting me right now, but I just can't see what I am doing wrong. Any suggestions?

TIA

 

[JLGWhiz]

Try this modified statement:

Set rFound = ActiveSheet.Range("C1:C" & Range("C65535").End(xlUp).Row).Find(What:=chkVal, _
                After:=Range("C1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

 

[Atroxell]

Thanks for the response! That does work, and I'll use it for now.

But my stubbornness will prevail eventually. I originally wanted to use a dynamically named range (named INVSupplierID), rather than the standard range definition.

Any suggestions on how it might work with a named range?

 

[JLGWhiz]

You could use INVSupplierID as a variable and do this:

Dim INVSupplierID As Range
Set INVSupplierID = ActiveSheet.Range("C1:C" & Range("C65535").End(xlUp).Row)

And then use the variable throughout the code for the range. If you add or delete rows or data, the variable will still contain all rows ith data in them. But you would not be able to use the variable in formulas on the worksheets as a named range.

I need to clarify the fact that the range will reset to any changes each time you initialize the variable. So, unless you put it in some kind of loop, it would only initialize once within a procedure. It would not pick up any changes that occur within the procedure, unless it was in a loop which makes the changes.

 

[Atroxell]

Thank you for the additional response. My obligations have been spread a bit wide lately, so I did not get back to this particular thread until today. I apologize for that.

Defining a range in either manner works, but I was hoping to used an exisitng dynamically named range as the search range without having to "reinvent the wheel" by defining a range on the spreadsheet.

I thought maybe since the range was already defined at the spreadsheet levle I could just reference it and use it in a search. I'm still not convinced I can't, but I am also not convinced that I can at this point.

Thanks for the help!