Announcement

Collapse
No announcement yet.

UserForm To Search, Find & Return Data

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • UserForm To Search, Find & Return Data

    Hi, I plagiarised somebody elses spreadsheet in order to create a simple search tool using a userform. Unfortunately I didn't fully understand all the VBA code and consequently can't get it to search properly. My sheet has some simple data organised in rows to record a customer's job. In essence the sheet registers the customer, their unique reference, the job number we allocate it and the date received. I want to use the userform, with 2 comboxes to search by customer, then their reference and once the appropriate record has been shown the 2 text boxes display our job number and the date.

    I'd be very grateful if someone could show me the error of my ways.

    I've attached a simplified version of my sheet.

    Regards
    Attached Files

  • #2
    Re: Use Of Userform To Search Data In Sheet

    i think its just this line....

    Code:
     d = ws.Range("a2", ws.[s65536].End(xlUp)).Value
    
    'Change to this
    
     d = ws.Range("a2", ws.[g65536].End(xlUp)).Value
    HTH

    z

    Comment


    • #3
      Re: Use Of Userform To Search Data In Sheet

      Thanks Z, that's definitely an improvement. I've now added the following new row :

      a0614a 11/11/2007 J0005 TECH Mar018 2 N

      but it shows the job number as 1 in the search when I expected it to show 5 !!

      Comment


      • #4
        Re: Use Of Userform To Search Data In Sheet

        Try placing this in your code

        Code:
        Private Sub CboOrderNo_Change()
        
            Dim xlLongRow As Long
            Dim xlStringValue As String
            Dim xlInt As Integer
            Dim xlWrkSht As Worksheet
            
                Set xlWrkSht = Sheets("Register")
                    xlLongRow = xlWrkSht.Cells(Rows.Count, 5).End(xlUp).Row
                
                xlStringValue = Me.CboOrderNo.Value
                
                For xlInt = 2 To xlLongRow Step 1
                    If xlWrkSht.Cells(xlInt, 5).Value = xlStringValue Then
                        Me.TxtJobNo.Value = xlWrkSht.Cells(xlInt, 3).Value
                        Me.TxtDate.Value = Format(xlWrkSht.Cells(xlInt, 2).Value, "dd-mmm-yy")
                            Exit For
                    End If
                Next xlInt
                
                Set xlWrkSht = Nothing
        
            
        End Sub
        your looking at the list index which works for the first instance of the A0613A... it won't work on the others...

        HTH

        z

        Comment


        • #5
          Re: Use Of Userform To Search Data In Sheet

          That works a treat. I eventually want to use this on another sheet which has more columns, say up to P and show more data on the search userform. Is there a way to use this sort of search function with the userform to allow the user to find the data in the first place, view it on the userform, edit some elements of it and then have the amended data replaced on the sheet?

          [Additional questions removed. Read & adhere to the rules please, or have posting rights revoked]

          Grateful for all help received.

          Regards
          Last edited by Dave Hawley; November 19th, 2007, 10:28.

          Comment


          • #6
            Re: Use Of Userform To Search Data In Sheet

            See, read & learn the Find Method.

            Comment

            Working...
            X