Search functionality,copy and paste

[Closed]
Report
-
Posts
2
Registration date
Monday April 27, 2015
Status
Member
Last seen
April 27, 2015
-
Hello,
I am new to VBA and looking for a search functionality macro.I am looking for three steps;-

There are three sheets,in sheet one i just created two textbox and assigned macros for search and clear.

Working of search macro:-
1) Clicking on search,it would ask for some input,any one could enter the ifnromation.

2) It would search into sheet 2 under one column whether the particular entry matches with the input.If yes,It should copy all those rows and paste into the second row of sheet 3.

3) if its not matching,it should ask for another input.

4) Second textbox would clear the information after click from sheet 3.

Please help.


1 reply

Posts
2
Registration date
Monday April 27, 2015
Status
Member
Last seen
April 27, 2015

Public strData2 As String
Public strSearchSheet As String
Public strRange2 As String

Sub NewSearch()


'User Prompt box to enter IO driver type
strData2 = InputBox(Prompt:="Enter the keyword", _
Title:="ENTER TYPE", Default:="Type here")

'strSearchSheet = InputBox(Prompt:="Enter Sheet name to search. (IE AI).", _
Title:="ENTER TYPE", Default:="Type here")
'User Prompt box to enter Column to search
'strRange2 = InputBox(Prompt:="Enter Column you wish to search. (IE E).", _
'Title:="ENTER TYPE", Default:="Type here")

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

'On Error GoTo Err_Execute

'Start search in row 3
Worksheets("Sheet6").Activate


LSearchRow = 3

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column = "TEXT", copy entire row to Sheet2
If Range(strRange2 & CStr(LSearchRow)).Value = strData2 Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet2 in next row
Sheets("Test").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("AO").Select

End If

LSearchRow = LSearchRow + 1

Wend


'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."


End Sub
Posts
2
Registration date
Monday April 27, 2015
Status
Member
Last seen
April 27, 2015

This is what i have searched so far from google.Please help me out.