Problem using .find

[Diving_Dan]

Hi all,

Having some problems trying to use .find to activate a cell. I have a worksheet called orders and in Column A I'm using the following function to pull unique order numbers from the table as most are duplicated.

=UNIQUE(Table1[Order Number])

I've got a userform that pulls in the order numbers ok from this column using named range "Unique_Orders" into a combobox.

What I want to happen is for when I change the order number on the form for the cell on the Orders worksheet to be activated when I select the order number from a combobox. I tried using the following code but I get run time error 91.

Private Sub cboOrderNumber_Change()
    
    Cells.Find(What:=cboOrderNumber.Value).Activate
    
End Sub

Any help on with this will be greatly appreciated.

Dan

 

[Rick Rothstein]

You probably need to specify some of the other arguments for the Find method. One of the problems with the Find method and Replace method is they 'remember' values for almost all of their arguments from the last time they were used whether that last use was via a VBA code line OR via the Find/Replace dialog box. For example, if the last time you used Find you specified "Match case", then the next time you used Find, it would look for an exact case match unless you specifically set that argument to False.

 

[Diving_Dan]

I have tried typing the order numbers in manually to the worksheet and the find code works fine and does what I want, it just won't select any of the cells with the unique function in. Any suggestions on which arguments to try or a different method to do what I am trying to achieve?

 

[Rick Rothstein]

That would normally indicate that your UNIQUE function call is not returning the value you think it is. Perhaps your data has a hidden character attached to it (a normal space or maybe a non-breaking space). It is kind of hard for us to guess the problem without seeing your exact setup. Can you post a copy of the workbook (desensitize any sensitive data) so that we can examine exactly what you have? If so, make sure to use the instructions in the yellow banner at the top of the webpage to post it here.

 

[Fluff]

You will need to specify to lookin values, rather than formulas

 

[Diving_Dan]

Thank you both, I was just about to upload a redacted copy and Fluff you nailed it. 3 1/2 Months in hospital on too much medication has fried my head a bit and I'm missing simple solutions like this. Thanks again

 

[Rick Rothstein]

Just so you know, LookIn is one of the arguments to the Find (and Replace) method that is remembered from a previous use. As I tried to indicate in my first post, it is usually a good idea to specify the setting you want for each of the arguments to the Find (and Replace) method when you use it, that way you will not run into the problem that you did this time.