+ Reply to Thread
Results 1 to 2 of 2

Search closest matching row from a UserForm entry, copy match, paste range to ActiveRow

  1. #1
    Registered User
    Join Date
    11-18-2012
    Location
    Australia
    MS-Off Ver
    2007
    Posts
    2

    Smile Search closest matching row from a UserForm entry, copy match, paste range to ActiveRow

    Hi there

    I have been searching, trialling (and failing) for a solution to my problem for three weeks now, but I can’t seem to solve it as I’m a complete VBA beginner! But I’m loving learning new things as I go.

    I am trying to find a way to search for the most recent matching row entry (in the same sheet) when a new text value is entered via a UserForm on column A. Then, copy cells B:E into the active row.

    To explain my goal step-by-step:
    1. When a blank cell in column A is clicked, the UserForm named NewEntry is called.
    2. When a user selects an existing name from the ListBox and clicks submit on the UserForm, it populates the active cell with this selection. Alternatively, the user can freetext a new name entry into the TextBox and click submit to populate the active cell.
    3. What I want to do next (and I can’t figure this out) is:
    4. When a new entry is submitted, search up the spreadsheet to find the most recent (i.e. the highest row number) name match in column A.
    5. If a match is found, copy this matching row data from column B to column E (i.e. not the entire row)
    6. Then, paste this matching data into the same range in the active row. The intent is to pre-fill information for users to avoid excess data entry.

    I’ve been fumbling around with the following code placed in the UserForm itself:

    Sub FindAndCopyAdjacent()
    Dim Cell As Range
    Set Cell = Range(ActiveCell).Find(What:=TextName.Text & ListName.Text)
    If Not Cell Is Nothing Then
    Range(Cell, Cell.Offset(0, 1)).Copy Destination:=("B:H")
    'need more info to determine destination range
    End If
    End Sub

    But I’m sure this is way off track.

    I would be so incredibly grateful if someone could help me.
    I have attached a sample workbook.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    11-18-2012
    Location
    Australia
    MS-Off Ver
    2007
    Posts
    2

    Re: Search closest matching row from a UserForm entry, copy match, paste range to ActiveRo

    Hi, an Ozgrid user StephenR has kindly solved this for me.

    (visit http://www.ozgrid.com/forum/showthread.php?t=173908 )

    Below is the corrected code that works wonderfully:


    Private Sub CommandButton1_Click()

    Dim Ctrl As Control

    Set Ctrl = ActiveControl

    If TypeName(Ctrl) = "ListBox" Then 'when the cursor is active in a ListBox, enter this data into the spreadsheet
    Select Case Ctrl.name
    Case Is = "ListName"
    MsgBox Ctrl.name & " selection = " & Ctrl.Value 'This adds a message box that confirms the selection
    End Select
    End If

    Set Ctrl = ActiveControl

    If TypeName(Ctrl) = "TextBox" Then 'when the cursor is active in a TextBox, enter this data into the spreadsheet
    Select Case Ctrl.name
    Case Is = "TextName"
    MsgBox Ctrl.name & " selection = " & Ctrl.Value 'This adds a message box that confirms the selection
    End Select
    End If

    ActiveCell.Value = TextName.Text & ListName.Text 'paste textbox selection AND listbox selection into the active cell
    Me.Hide 'closes the UserForm when the action is performed

    Set Cell = Columns(1).Find(What:=Ctrl.Value, After:=Range("A1"), LookAt:=xlWhole, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) 'this next code searches up the sheet to find a name match
    If Not Cell Is Nothing Then
    Cell.Offset(0, 1).Resize(, 4).Copy Range("A" & Rows.Count).End(xlUp).Offset(, 1) 'then pastes a range from the matching row to the current row

    End If
    End Sub


    I needed to amend my existing button code and there was no need for the extra macro.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1