Results 1 to 2 of 2

Thread: Updating a Userform using a multi-column Search

  1. #1
    Neophyte Denny57's Avatar
    Join Date
    Jun 2020
    Posts
    2
    Articles
    0
    Excel Version
    365

    Updating a Userform using a multi-column Search



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi

    I Have created a file with a userform through which I can Add, Search and Update records in a single worksheet by searching for a unique value in a single column. The code works well, so now I am working on a similar file, but the search needs to find a unique value which might be contained in one of up to 12 columns.

    My user form has a dedicated textbox into which I enter the unique 5 or 6 digit number to be found and a dedicated Command Button to initiate the search.

    I have reached the point where I need to code up the Command Button to initiate this search but the code I used for a single column is not appropriate in this instance

    My aims are:-
    1) Enter a 5 - 6 digit number into a dedicated textbox on the user form (txtSearch)
    2) Initiate a search for that number in named range (of 12 columns and 6000 rows (CmdSearch)
    3) If no Match - Return a message box "Match Not Found"
    4) If Match - Re-populate the user form with all the details from the matched row with the corresponding records details

    The following is an extract of paer of the code (the rest just re-populates the remaining 20 or so user form fields)

    Code:
    Private Sub cmdSearch_Click()
    
    
    Dim sh As Worksheet, f As Range, i As Long
    Set sh = Sheets("Sheet2")
    Set f = sh.Range("O:Z").Find(Val(txtSearch.value), , xlValues, xlWhole)
    If Not f Is Nothing Then
        txtRecordNumber .value = sh.Cells(f.Row, 1).value
        cboRecordType.value = sh.Cells(f.Row, 2).value
        txtRecordClass.value = sh.Cells(f.Row, 3).value
    NOTE: Range "O:Z" are the 12 rowns but I have also enclosed these in a Named Range if that helps.

    I welcome any assistance
    Last edited by p45cal; 2020-06-22 at 01:25 AM. Reason: added code tags

  2. #2
    Neophyte Denny57's Avatar
    Join Date
    Jun 2020
    Posts
    2
    Articles
    0
    Excel Version
    365
    Solved

Tags for this Thread

Posting Permissions

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