Consulting

Results 1 to 5 of 5

Thread: Help with Find and replace code

  1. #1
    VBAX Newbie
    Joined
    Jan 2022
    Posts
    3
    Location

    Help with Find and replace code

    Hi, any help appreciated with using VBA to find data in a table based on user input from a Drop Down Box.

    I have attached a Sample Workbook

    Data is contained in a Table on Sheet 2 (The real world table is bigger but I reckon if I can be shown how to make this work I'll be able to adapt it to the real world Table

    I'd like the User to select a Name from a drop down list of names in Sheet 1 Cell "A6"

    This should bring up the related data from Sheet2 in the next cells in that row for the user to then edit/change.

    When the Update button is pressed the new data is copied back to the orriginal table.

    I'm generally a "Record Macro" kind of user, which works in most cases I need it to but VBA seems reluctant to let me use a drop down box to find data elsewhere when I try to record a macro.

    Thanks for any help.

    Rob
    Attached Files Attached Files

  2. #2
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    One method might be:

    Dim FoundRow As Long
    
    Sub FindData()
        Dim FindName As String
    
        FindName = Sheet1.Range("A6").Value
        FoundRow = Sheet2.Range("B:B").Find(FindName, , xlValues, xlWhole).Row
        Union(Sheet2.Range("C" & FoundRow), Sheet2.Range("D" & FoundRow), Sheet2.Range("F" & FoundRow), Sheet2.Range("H" & FoundRow)).Copy
        Sheet1.Range("B6").PasteSpecial xlAll
        Application.CutCopyMode = false
    End Sub
    
    Sub ReturnRow()
        With Sheet2
            .Range("C" & FoundRow) = Sheet1.Range("B6")
            .Range("D" & FoundRow) = Sheet1.Range("C6")
            .Range("F" & FoundRow) = Sheet1.Range("D6")
            .Range("H" & FoundRow) = Sheet1.Range("E6")
        End With
    End Sub
    Place it in a module and point your buttons to the code. Keep the Dim at the top out of the two subs.

    There would be quite a few ways of doing this but the above is a start.

    Hope this helps
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Probably more elegant and more efficient ways to do this


    Option Explicit
    
    
    Sub sName()
        Dim oList As ListObject
        
        Set oList = Worksheets("Sheet2").ListObjects("Table1")
        
        With Worksheets("Sheet1")
            
            On Error GoTo NiceExit
            .Cells(6, 2).Value = Application.VLookup(.Cells(6, 1).Value, oList.DataBodyRange, 2, False)
            .Cells(6, 3).Value = Application.VLookup(.Cells(6, 1).Value, oList.DataBodyRange, 3, False)
            .Cells(6, 4).Value = Application.VLookup(.Cells(6, 1).Value, oList.DataBodyRange, 5, False)
            .Cells(6, 5).Value = Application.VLookup(.Cells(6, 1).Value, oList.DataBodyRange, 7, False)
    
    
            Exit Sub
        End With
        
    NiceExit:
    End Sub
    
    
    
    
    Sub uUpdate()
        Dim r As Long
        Dim oList As ListObject
        
        Set oList = Worksheets("Sheet2").ListObjects("Table1")
        
        With Worksheets("Sheet1")
            r = Application.Match(.Cells(6, 1).Value, oList.ListColumns(1).Range, 0)
            
            oList.ListRows(r - 1).Range.Cells(2).Value = .Cells(6, 2).Value
            oList.ListRows(r - 1).Range.Cells(3).Value = .Cells(6, 3).Value
            oList.ListRows(r - 1).Range.Cells(5).Value = .Cells(6, 4).Value
            oList.ListRows(r - 1).Range.Cells(7).Value = .Cells(6, 5).Value
        End With
        
        Exit Sub
    
    
    NiceExit:
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Newbie
    Joined
    Jan 2022
    Posts
    3
    Location
    Wow That was quick, and both solutions look elegant to my untrained eyes. Certainly much easier to follow than some of the code I'd tried to reconfigure for my needs.
    Thank you both so much, made my life a whole lot easier

  5. #5
    VBAX Newbie
    Joined
    Jan 2022
    Posts
    3
    Location
    ok, follow up question.
    I now need to have the option in the above code to select and then change the name selected to something else.
    The way I would do this as a one off is find and replace but I can't find vba code for that which works with a variable "find what" option?
    Thanks for any help.

Posting Permissions

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