Copy & paste data from selected row in search box result

UNIC0RN

New Member
Joined
Oct 9, 2017
Messages
13
I have managed to create a userform in "Master" workbook which the function is to 'find all' in all worksheet from "Membership" workbook based on value added at text box and its working well, but it is halfway from my target. I need an assistance from expert to find out how to :- auto populate in the search textbox base on value "E17" from "Master" workbook.
- copy & paste certain data back to "Master" workbook by clicking the selected row (or maybe need a cmd button?) based on value choosed in result textbox as below:
"Membership" F cell value to "Master" cell S17
"Membership" J cell value to "Master" cell E17
"Membership" K cell value to "Master" cell S19
"Membership" L cell value to "Master" cell S21
"Membership" N cell value to "Master" cell E21

Thank you in advance.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
This is the code that applied on the FindAll userform.
Code:
Private Sub TextBox_Find_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Call FindAllMatches   
End Sub
 
Private Sub Label_ClearFind_Click()
Me.TextBox_Find.Text = ""
Me.TextBox_Find.SetFocus   
End Sub
 
Sub FindAllMatches()
Dim SearchRange As Range
Dim FindWhat As Variant
Dim FoundCells As Range
Dim FoundCell As Range
Dim arrResults() As Variant
Dim lFound As Long
Dim lSearchCol As Long
Dim lLastRow As Long  
If Len(f_FindAll.TextBox_Find.Value) > 1 Then 
Set SearchRange = ActiveSheet.UsedRange.Cells
        FindWhat = f_FindAll.TextBox_Find.Value
        Set FoundCells = FindAll(SearchRange:=SearchRange, _
                                FindWhat:=FindWhat, _
                                LookIn:=xlValues, _
                                LookAt:=xlPart, _
                                SearchOrder:=xlByColumns, _
                                MatchCase:=False, _
                                BeginsWith:=vbNullString, _
                                EndsWith:=vbNullString, _
                                BeginEndCompare:=vbTextCompare)
        If FoundCells Is Nothing Then
            ReDim arrResults(1 To 1, 1 To 2)
            arrResults(1, 1) = "No Results"
Else
            ReDim arrResults(1 To FoundCells.Count, 1 To 2)
            lFound = 1
            For Each FoundCell In FoundCells
                arrResults(lFound, 1) = FoundCell.Value
                arrResults(lFound, 2) = FoundCell.Address
                lFound = lFound + 1
            Next FoundCell
End If
                Me.ListBox_Results.List = arrResults
Else
        Me.ListBox_Results.Clear
End If
End Sub
 
Private Sub ListBox_Results_Click()
Dim strAddress As String
Dim l As Long
    For l = 0 To ListBox_Results.ListCount
        If ListBox_Results.Selected(l) = True Then
            strAddress = ListBox_Results.List(l, 1)
            ActiveSheet.Range(strAddress).Select
            GoTo EndLoop
End If
Next l
 
EndLoop:
 End Sub
 
Upvote 0

Forum statistics

Threads
1,223,704
Messages
6,173,984
Members
452,540
Latest member
haasro02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top