andre30331
New Member
- Joined
- May 14, 2014
- Messages
- 29
Hello,
Here is my issue I currently have code that, when activated it displays an input box for the user to enter search criteria. Once entered and the script is ran it searches sheet1 and paste the findings onto sheet2. Also the current code when pasting the result to Sheet2 begins pasting at row two(2) instead of row one(1) and I cannot seem to fix it but this is not the main issue.
The main issue:
What I would like this code to do is, instead of having an “input box” populate when the Macro activates is use a predetermined cell on sheet1 lets say cell A1 to act as a trigger to run the Marco and search for the text entered into cell A1. Having this option would allow the user to search for any number of items much quicker and look to sheet2 for the results without having to repeatedly navigate into developer options to run the macro. Thanks in advance for helping. Here is the current code I am using:
Sub SearchForString()
Dim LSearchValue As String
Dim RefCount As Integer
LSearchValue = InputBox("Please enter a value to search for.", "Enter value") & "*"
RefCount = Application.WorksheetFunction.CountIf(Sheet1.Range("A:L"), LSearchValue) - 1
Range("E:E").Activate
For Counter = 0 To RefCount Step 1
With Columns("A:L").Find(What:=LSearchValue, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
.EntireRow.Copy Destination:=Worksheets("sheet2").Range("A65536").End(xlUp).Offset(1, 0)
.Activate
End With
Next
End Sub
Here is my issue I currently have code that, when activated it displays an input box for the user to enter search criteria. Once entered and the script is ran it searches sheet1 and paste the findings onto sheet2. Also the current code when pasting the result to Sheet2 begins pasting at row two(2) instead of row one(1) and I cannot seem to fix it but this is not the main issue.
The main issue:
What I would like this code to do is, instead of having an “input box” populate when the Macro activates is use a predetermined cell on sheet1 lets say cell A1 to act as a trigger to run the Marco and search for the text entered into cell A1. Having this option would allow the user to search for any number of items much quicker and look to sheet2 for the results without having to repeatedly navigate into developer options to run the macro. Thanks in advance for helping. Here is the current code I am using:
Sub SearchForString()
Dim LSearchValue As String
Dim RefCount As Integer
LSearchValue = InputBox("Please enter a value to search for.", "Enter value") & "*"
RefCount = Application.WorksheetFunction.CountIf(Sheet1.Range("A:L"), LSearchValue) - 1
Range("E:E").Activate
For Counter = 0 To RefCount Step 1
With Columns("A:L").Find(What:=LSearchValue, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
.EntireRow.Copy Destination:=Worksheets("sheet2").Range("A65536").End(xlUp).Offset(1, 0)
.Activate
End With
Next
End Sub