Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: UserForm To Search, Find & Return Data

  1. #1
    Join Date
    29th April 2006
    Location
    UK
    Posts
    31

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,377

    Re: Use Of Userform To Search Data In Sheet

    i think its just this line....

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

    z

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    29th April 2006
    Location
    UK
    Posts
    31

    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 !!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,377

    Re: Use Of Userform To Search Data In Sheet

    Try placing this in your code

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    29th April 2006
    Location
    UK
    Posts
    31

    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 at 10:28.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Use Of Userform To Search Data In Sheet

    See, read & learn the Find Method.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. UserForm To Search, Edit, Add & Delete Data
    By leemarquis in forum EXCEL HELP
    Replies: 4
    Last Post: March 17th, 2008, 12:02
  2. Userform Search & Enter Data on Same Row
    By nu@this in forum EXCEL HELP
    Replies: 1
    Last Post: June 30th, 2007, 15:24
  3. Search And Insert Data Userform
    By mini12 in forum EXCEL HELP
    Replies: 2
    Last Post: March 27th, 2007, 22:00
  4. Lookup / Find / Search value then return value
    By P3r3g in forum EXCEL HELP
    Replies: 8
    Last Post: October 8th, 2005, 14:02
  5. Search Data Userform
    By mini12 in forum EXCEL HELP
    Replies: 26
    Last Post: August 7th, 2005, 09:20

Bookmarks

Posting Permissions

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