Consulting

Results 1 to 19 of 19

Thread: Formula help please

  1. #1
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    1,942
    Location

    Formula help please

    Im trying to create a searchable combo box on a worksheet. There is a #Value error in cell F7

    =$D$2:INDEX($D$2:$D$12,COUNTIF($D$2:$D$12,"?*")) 
    
    
    Formatting tags added by mark007
    Does anyone see the error? The issue seems to be the wildcard "?*" Workbook is attached. Thanks.

    If I can get this to work is there a way to do this in a userform?
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    Peace of mind is found in some of the strangest places.

  2. #2
    What is the purpose? What result were you expecting?

  3. #3
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    1,942
    Location
    Hi Ken. I got it sorted. Is there a way to use a combo bos like the one in the WB attached in a user form?
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    Peace of mind is found in some of the strangest places.

  4. #4
    It is an ActiveX control so, yes, same method can be used.

  5. #5
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    1,942
    Location
    Quote Originally Posted by Kenneth Hobs View Post
    It is an ActiveX control so, yes, same method can be used.
    so on the combo box in the userform would the VBA CLICK code be. Userform.1ComboBox1.Employees? Or do you have to use a module or reference the sheet?
    Peace of mind is found in some of the strangest places.

  6. #6
    Either the Change event or Click event gets you close to what the other does. Of course rather than ListFillRange=Employees set the RowSource=Employees.

    Private Sub ComboBox1_Change() 
        ComboBox1.DropDown 
    End Sub 
     
     
    Private Sub ComboBox1_Click() 
         'ComboBox1.DropDown
    End Sub 
    
    
    Formatting tags added by mark007

  7. #7
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    1,942
    Location
    that got it to show the list but instead of just showing the names say with "am" in it like the ones on the combo box one the sheet does the drop down shows all names at all times. Update WB with form attached.
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    Peace of mind is found in some of the strangest places.

  8. #8
    I have about 3 ways to do it I think. I will try a few 8 hours or so from now and show you.

  9. #9
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    1,942
    Location
    Quote Originally Posted by Kenneth Hobs View Post
    I have about 3 ways to do it I think. I will try a few 8 hours or so from now and show you.
    thanks ken
    Peace of mind is found in some of the strangest places.

  10. #10
    Here is the Filter() method.
    Private Sub UserForm_Initialize() 
        Dim a 
        a = Application.Index(Range("A2", Cells(Rows.Count, "A").End(xlUp)).Value, 0, 0) 
        ComboBox1.List = a 
    End Sub 
     
     
    Private Sub ComboBox1_Change() 
        Dim a, s() As String 
        On Error Resume Next 
        a = Application.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp))) 
        With ComboBox1 
            If .Value = "" Then .List = a 
            s() = Filter(Application.Transpose(.List), .Text, True, vbTextCompare) 
            If UBound(s) <> -1 Then .List = Application.Transpose(s) 
        End With 
    End Sub 
     
     
    Private Sub btnExit_Click() 
        Unload Me 
    End Sub 
    
    
    Formatting tags added by mark007
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  11. #11
    Here is a 2nd method using your Sheet's ActiveX Combobox's ListFillRange method with your dynamic named range.

    For a large list, the 1st method would probably be best. For short lists, this 2nd method is more simple providing your setup for dynamic range and formulas are already done.

    Normally, I prefer Filter() since I like to remove duplicates and sort.
    Private Sub UserForm_Initialize() 
        With Sheet1 
            [G1] = "" 
            ComboBox1.RowSource = .[Employees].Address(external:=True) 
        End With 
    End Sub 
     
     
    Private Sub ComboBox1_Change() 
        With Sheet1 
            [G1] = ComboBox1.Text 
            ComboBox1.RowSource = .[Employees].Address(external:=True) 
        End With 
    End Sub 
     
     
    Private Sub btnExit_Click() 
        Unload Me 
    End Sub 
    
    
    Formatting tags added by mark007

  12. #12
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    1,942
    Location
    interesting....thanks for that but i do have a question. In the latest WB say you type in "e" you get all names in the drop down that contain the letter "E", "f" the same thing. But if yo start to type "G" there are at least to names wit the letter "g" in them but what it does is bring up the first name in the list.

    What appears to be happening is that if a name does not start with a letter you are typing all entries with that letter show. If there is a name that starts with that letter only the first occurrence is presented and not all names with the letter in it.
    Peace of mind is found in some of the strangest places.

  13. #13
    Some combobox properties are critical to get the drilldown that you want.

    This one has the 3 methods. It has a 4th, or 3rd userform but don't run it. It was an old method that I used for ListBox drilldown years ago. It is a less efficient way anyway.

    I did a small tweak to the Filter method. Now, if one backspace deletes, it will go back to that wildard drilldown.
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  14. #14
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    1,942
    Location
    i like the dynamic combo box. i noticed it fills the other combo box with the potential matches (at least that is what it appears to do). Is there a way to just use the dynamic combo box and have the filtered entries show as you type? also what code goes in the module?
    Peace of mind is found in some of the strangest places.

  15. #15
    Yes, it uses the same setup as the sheet1.combobox1. So, your formulas and dynamic named range need to be there. Since it uses the same method by modifying G1, the ListFillRange in sheet1.combobox1 adjusts as well.

    Code for it is in the userform ufFilterCombo2 which is also shown in post #11. I guess you could put it in a Module. If you had many userform's using that method, I guess one might pass MSForms.Combobox to such.

    I prefer to let the user click the dropdown arrow but if you like, in ufFilterCombo2:
    Private Sub ComboBox1_Enter() 
        ComboBox1.DropDown 
    End Sub 
    
    
    Formatting tags added by mark007
    For many such drilldown lists, I would use the Filter() method. I don't want to worry about setting up all of those formulas and dynamic named ranges. But then I am lazy...

  16. #16
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    1,942
    Location
    hi ken. im going to have around 70000 names to search using this. the list is also going to be updated once a month. do the entries have to be sorted in order for this to work? it appears that they dont because you are searching characters and not full names so the order doesnt matter, correct?
    Peace of mind is found in some of the strangest places.

  17. #17
    Right, it is not needed.

    If I were the end user, I would want duplicates removed and sorted.

  18. #18
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    1,942
    Location
    thanks for all the help. if i were to incorporate this (the filter method) into this WB/form, where would the code go and what code would be needed. Im not sure since we have been through a bunch of examples.

    In the form that opens on the attached WB, I would like to replace the account name text box to a combo box to perform this search. Would you please tell me where to make the adjustments? Thanks.
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    Peace of mind is found in some of the strangest places.

  19. #19
    You lost me. I don't know what the textbox control name is. I don't know what column of data you want to filter in it.

    Post #13's 1st userform has the code similar to post #10.
    Private Sub UserForm_Initialize() 
        Dim a 
        a = Application.Index(Range("A2", Cells(Rows.Count, "A").End(xlUp)).Value, 0, 0) 
        ComboBox1.List = a 
    End Sub 
     
     
    Private Sub ComboBox1_Change() 
        Dim a, b 
        On Error Resume Next 
        a = Application.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp))) 
        With ComboBox1 
            If .Value = "" Then .List = a 
             'b = Filter(Application.Transpose(.List), .Text, True, vbTextCompare)
            b = Filter(a, .Text, True, vbTextCompare) 
            If UBound(b) <> -1 Then .List = Application.Transpose(b) 
        End With 
    End Sub 
    
    
    Formatting tags added by mark007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •