none
Excel Searching with a User form RRS feed

  • Question

  • Hi, I am working on an excel vba project and  i am not very good with vba. So far, I've done up a userform which is mainly to search from a sheet of data. The search is done with two requirements, a name and an id number and then it will display the whole row of investments; bonds, equities, unit trusts etc.. Currently, my codes are using newrow and isfound to search for the data and display it on a userform.

    However, in the excel, I found out that there is the same person with the same id number with different kinds of investments such as derivatives, structured products. But when i do the search, it displays the row which is found first. Is there any way I can search for this person with their name and ic and show ALL of their investments in one userform? Much help is appreciated. Thank you.

    Tuesday, August 18, 2015 7:10 AM

Answers

  • >>>Is there any way I can search for this person with their name and ic and show ALL of their investments in one userform

    According to your description, I suggest that you could use the use of ActiveX Data Objects (ADO) with Microsoft Excel spreadsheets as a data source.
    You could refer to below code:

    On Error Resume Next
    
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H0001
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordSet = CreateObject("ADODB.Recordset")
    
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Scripts\Test.xls;" & _
            "Extended Properties=""Excel 8.0;HDR=Yes;"";" 
    
    objRecordset.Open "Select * FROM [Sheet1$] Where Number = 2", _
        objConnection, adOpenStatic, adLockOptimistic, adCmdText
    
    Do Until objRecordset.EOF
        Wscript.Echo objRecordset.Fields.Item("Name"), _
            objRecordset.Fields.Item("Number")
        objRecordset.MoveNext
    Loop
    

    For more information, click here to refer about How To Use ADO with Excel Data from Visual Basic or VBA

    Friday, August 21, 2015 9:59 AM
  • Is there any way I can search for this person with their name and ic and show ALL of their investments in one userform?

    Sure, at 1st find all (see code below), then show all.

    The question is what you mean with "display it on a userform". What kind of control do you use?

    Andreas.

    Function FindAll(ByVal Where As Range, ByVal What, _
        Optional ByVal After As Variant, _
        Optional ByVal LookIn As XlFindLookIn = xlValues, _
        Optional ByVal LookAt As XlLookAt = xlWhole, _
        Optional ByVal SearchOrder As XlSearchOrder = xlByRows, _
        Optional ByVal SearchDirection As XlSearchDirection = xlNext, _
        Optional ByVal MatchCase As Boolean = False, _
        Optional ByVal SearchFormat As Boolean = False) As Range
      'Find all occurrences of What in Where (Windows version)
      Dim FirstAddress As String
      Dim C As Range
      'From FastUnion:
      Dim Stack As New Collection
      Dim Temp() As Range, Item
      Dim i As Long, j As Long
    
      If Where Is Nothing Then Exit Function
      If SearchDirection = xlNext And IsMissing(After) Then
        'Set After to the last cell in Where to return the first cell in Where in front if _
          it match What
        Set C = Where.Areas(Where.Areas.Count)
        'BUG in XL2010: Cells.Count produces a RTE 6 if C is the whole sheet
        'Set After = C.Cells(C.Cells.Count)
        Set After = C.Cells(C.Rows.Count * CDec(C.Columns.Count))
      End If
    
      Set C = Where.Find(What, After, LookIn, LookAt, SearchOrder, _
        SearchDirection, MatchCase, SearchFormat:=SearchFormat)
      If C Is Nothing Then Exit Function
    
      FirstAddress = C.Address
      Do
        Stack.Add C
        If SearchFormat Then
          'If you call this function from an UDF and _
            you find only the first cell use this instead
          Set C = Where.Find(What, C, LookIn, LookAt, SearchOrder, _
            SearchDirection, MatchCase, SearchFormat:=SearchFormat)
        Else
          If SearchDirection = xlNext Then
            Set C = Where.FindNext(C)
          Else
            Set C = Where.FindPrevious(C)
          End If
        End If
        'Can happen if we have merged cells
        If C Is Nothing Then Exit Do
      Loop Until FirstAddress = C.Address
    
      'FastUnion algorithm © Andreas Killer, 2011:
      'Get all cells as fragments
      ReDim Temp(0 To Stack.Count - 1)
      i = 0
      For Each Item In Stack
        Set Temp(i) = Item
        i = i + 1
      Next
      'Combine each fragment with the next one
      j = 1
      Do
        For i = 0 To UBound(Temp) - j Step j * 2
          Set Temp(i) = Union(Temp(i), Temp(i + j))
        Next
        j = j * 2
      Loop Until j > UBound(Temp)
      'At this point we have all cells in the first fragment
      Set FindAll = Temp(0)
    End Function
    

    Friday, August 21, 2015 10:38 AM

All replies

  • Just find "ALL", then you can see "A" from "B".

    You'd better post detail codes.

    Without it nobody can give you a good advice which you need.

    Regards


    Tuesday, August 18, 2015 7:51 AM
  • >>>Is there any way I can search for this person with their name and ic and show ALL of their investments in one userform

    According to your description, I suggest that you could use the use of ActiveX Data Objects (ADO) with Microsoft Excel spreadsheets as a data source.
    You could refer to below code:

    On Error Resume Next
    
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H0001
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordSet = CreateObject("ADODB.Recordset")
    
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Scripts\Test.xls;" & _
            "Extended Properties=""Excel 8.0;HDR=Yes;"";" 
    
    objRecordset.Open "Select * FROM [Sheet1$] Where Number = 2", _
        objConnection, adOpenStatic, adLockOptimistic, adCmdText
    
    Do Until objRecordset.EOF
        Wscript.Echo objRecordset.Fields.Item("Name"), _
            objRecordset.Fields.Item("Number")
        objRecordset.MoveNext
    Loop
    

    For more information, click here to refer about How To Use ADO with Excel Data from Visual Basic or VBA

    Friday, August 21, 2015 9:59 AM
  • Is there any way I can search for this person with their name and ic and show ALL of their investments in one userform?

    Sure, at 1st find all (see code below), then show all.

    The question is what you mean with "display it on a userform". What kind of control do you use?

    Andreas.

    Function FindAll(ByVal Where As Range, ByVal What, _
        Optional ByVal After As Variant, _
        Optional ByVal LookIn As XlFindLookIn = xlValues, _
        Optional ByVal LookAt As XlLookAt = xlWhole, _
        Optional ByVal SearchOrder As XlSearchOrder = xlByRows, _
        Optional ByVal SearchDirection As XlSearchDirection = xlNext, _
        Optional ByVal MatchCase As Boolean = False, _
        Optional ByVal SearchFormat As Boolean = False) As Range
      'Find all occurrences of What in Where (Windows version)
      Dim FirstAddress As String
      Dim C As Range
      'From FastUnion:
      Dim Stack As New Collection
      Dim Temp() As Range, Item
      Dim i As Long, j As Long
    
      If Where Is Nothing Then Exit Function
      If SearchDirection = xlNext And IsMissing(After) Then
        'Set After to the last cell in Where to return the first cell in Where in front if _
          it match What
        Set C = Where.Areas(Where.Areas.Count)
        'BUG in XL2010: Cells.Count produces a RTE 6 if C is the whole sheet
        'Set After = C.Cells(C.Cells.Count)
        Set After = C.Cells(C.Rows.Count * CDec(C.Columns.Count))
      End If
    
      Set C = Where.Find(What, After, LookIn, LookAt, SearchOrder, _
        SearchDirection, MatchCase, SearchFormat:=SearchFormat)
      If C Is Nothing Then Exit Function
    
      FirstAddress = C.Address
      Do
        Stack.Add C
        If SearchFormat Then
          'If you call this function from an UDF and _
            you find only the first cell use this instead
          Set C = Where.Find(What, C, LookIn, LookAt, SearchOrder, _
            SearchDirection, MatchCase, SearchFormat:=SearchFormat)
        Else
          If SearchDirection = xlNext Then
            Set C = Where.FindNext(C)
          Else
            Set C = Where.FindPrevious(C)
          End If
        End If
        'Can happen if we have merged cells
        If C Is Nothing Then Exit Do
      Loop Until FirstAddress = C.Address
    
      'FastUnion algorithm © Andreas Killer, 2011:
      'Get all cells as fragments
      ReDim Temp(0 To Stack.Count - 1)
      i = 0
      For Each Item In Stack
        Set Temp(i) = Item
        i = i + 1
      Next
      'Combine each fragment with the next one
      j = 1
      Do
        For i = 0 To UBound(Temp) - j Step j * 2
          Set Temp(i) = Union(Temp(i), Temp(i + j))
        Next
        j = j * 2
      Loop Until j > UBound(Temp)
      'At this point we have all cells in the first fragment
      Set FindAll = Temp(0)
    End Function
    

    Friday, August 21, 2015 10:38 AM