Solved Excel Macro to Search for Key Words and copy results

January 13, 2016 at 06:41:51
Specs: Windows 7
Hello,
I need a macro that searches key words in columns B,C and D from Sheet 1 and returns values from column A in sheet 2 in column A.
It would be great if the input key words could be entered in a dialog box. The key words can be combinations of letters and numbers.
Thank you in advance!

See More: Excel Macro to Search for Key Words and copy results

Report •


#1
January 13, 2016 at 09:00:21
✔ Best Answer
This is quick and dirty, but should accomplish what you asked for. My guess is that there are more details that we will require before a complete solution can be offered.

Sub FindKeyWordBCD()
Dim myKey As String
Dim k As Range
Dim nxtRw As Long
'Get keyword from user
  myKey = Application.InputBox("Please Enter Key Word")
'Search for keyword
    With Sheets(1).Columns("B:D")
      Set k = .Find(myKey, lookat:=xlWhole)
'If found, copy Sheet1.Column A value to next empty row in Sheet2.Column A
        If Not k Is Nothing Then
         nxtRw = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row + 1
         Sheets(1).Cells(k.Row, 1).Copy Sheets(2).Cells(nxtRw, 1)
        End If
    End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 14, 2016 at 02:06:10
Thank you! I needed a macro that searches key words in columns B,C and D from Sheet 1 and returns values from column A in sheet 2 in column A. And I could not find such macro before. I created a dialog box to input key words could be entered. Looks great.))

Report •

Related Solutions


Ask Question