Excel 2007 : Enter a value in an input box and then search for this value in a column 2007

[Pipski121]

Hi,

I hope someone can help me. I can't write code but I copy and paste useful stuff that other people have kindly written and posted on the internet. I think the problem I have will be a pretty easy one to solve for someone with a bit of knowledge.

What I want to do is search a column for a value and then copy the entire row into a new worksheet. I want to be able to search using an input box as the values will be variable. The cells in the column will contain other data, as well as the searched value.

I have found a code on the internet that nearly does what I want but it looks for an exact match of the value in the cell and then copies the row. What I want to do is edit this code so it will copy the row if it contains the searched value, instead of looking for an exact match.

I've pasted the code I've found below - hope someone can help, thanks.

Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim LSearchValue As String

On Error GoTo Err_Execute

LSearchValue = InputBox("Please enter a value to search for.", "Enter value")

'Start search in row 4
LSearchRow = 4

'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 E = LSearchValue, copy entire row to Sheet2
If Range("E" & CStr(LSearchRow)).Value = LSearchValue Then

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

'Paste row into Sheet2 in next row
Sheets("Sheet2").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("Sheet1").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:

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[Pipski121]

Hi,

I hope someone can help me. I can't write code but I copy and paste useful stuff that other people have kindly written and posted on the internet. I think the problem I have will be a pretty easy one to solve for someone with a bit of knowledge.

What I want to do is search a column for a value and then copy the entire row into a new worksheet. I want to be able to search using an input box as the values will be variable. The cells in the column will contain other data, as well as the searched value.

I have found a code on the internet that nearly does what I want but it looks for an exact match of the value in the cell and then copies the row, I didn't write this code. I posted this code earlier and got an automated message saying I'd broken the forum rules, mentioning something about code tags. I won't post it again as I don't understand what this means

To give an example of what I want. The cell in the search column will contain data such as - MUFC v LUFC 23.03.11. I'd like to be able to search for either MUFC or LUFC in a given column and copy the details of the row that contains this information to another workbook

I'd be grateful of any help.

Thanks