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
    2,033
    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,"?*"))
    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
    Peace of mind is found in some of the strangest places.

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    What is the purpose? What result were you expecting?

  3. #3
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    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
    Peace of mind is found in some of the strangest places.

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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
    2,033
    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
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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

  7. #7
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    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
    Peace of mind is found in some of the strangest places.

  8. #8
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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
    2,033
    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
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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
    Attached Files Attached Files

  11. #11
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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

  12. #12
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    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
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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

  14. #14
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    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
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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
    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
    2,033
    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
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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
    2,033
    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
    Peace of mind is found in some of the strangest places.

  19. #19
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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

Posting Permissions

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