Find Value in cell via Inputbox, copy row, paste in new worksheet

[lthursdayl]

Hello Everyone,
I have been trying to write a macro in VB that will search for a word in a cell, the word would be input by the user via a inputbox, search the entire worksheet, then copy the entire row the word is found on, and paste the row into a new worksheet that is part of the same workbook. I am not attaching the code I have been working on because it is awful and I think I have been going about this in the wrong way. Any help is greatly appreciated.
Thanks in advance.

 

[Tom Urtis]

Is the VIN list a unique list, meaning are there any duplicated VINs, and if so, why would that be if every VIN by its nature is a unique number. I see several VINs in your list but I don't know if that is an actual list or a cobbled list.

 

[Carguy37122]

There will be duplicates, because I'm only searching for the 1st 8 digits. The 1st 8 digits reference the manufacturer, yr,make, model. The 9th digit is a check digit and the remaining digit are unique to the vehicle.

If you Google the 1st 8 digits of the vin for our your personal car, you will get a bunch of hits. Unless you drive an ultra rare car.


Your input box works beautifully!

It would be sweet to lookup multiple vin#s at one time.

I had to lookup 200 vin#s this week. Makes for a long day.

 

[Tom Urtis]

Is your list really 7 columns wide, if not how many columns is it and in any case (that is, whether or not it comprises 7 columns), which columns are they.

What else is on that worksheet besides the list.

What row is the header row for that list, example, is it row 1 or some other row.

 

[ashwinghanta]

Hello Tom,

I need a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> code which is very close to this thread's problem.

I have the following table with 4 rows and 8 columns. I would like to find a row with respect to the room name(for example Find room name -'a') and select any/multiple column name (for example type ,wall, door, floor) and paste this particular row with room name, type, wall, door, floor in a new sheet.



[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD]room name[/TD]
[TD]material[/TD]
[TD]type[/TD]
[TD]floor[/TD]
[TD]wall[/TD]
[TD]window[/TD]
[TD]door[/TD]
[TD]roof[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]concrete[/TD]
[TD]living[/TD]
[TD]marble[/TD]
[TD]painted[/TD]
[TD]small[/TD]
[TD]wood[/TD]
[TD]slope[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]brick[/TD]
[TD]hall[/TD]
[TD]tile[/TD]
[TD]plastered[/TD]
[TD]large[/TD]
[TD]metal[/TD]
[TD]plain[/TD]
[/TR]
</tbody>[/TABLE]


This row with column names on a new sheet.

[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD]room name[/TD]
[TD]type[/TD]
[TD]wall[/TD]
[TD]door[/TD]
[TD]floor[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]living[/TD]
[TD]painted[/TD]
[TD]wood[/TD]
[TD]marble[/TD]
[/TR]
</tbody>[/TABLE]



Is there a way to do this with <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym> code. If so, looking forward for your help.

Thanks
Ashwin

 

[jayjayGC]

Hi Tom,

Your code has been incredibly helpful to me! I've been searching for days for an effective way to do this! I do have another requirement, however, if you are willing to keep adding to your code...

I need the copied rows to only include some columns of the original sheet and not all of them... is there an easy way to recode for this?

Thanks so much for your assistance!!

 

[Tom Urtis]

There have been a few requests for modification of my original code way back when. Can you please...
(1) Post my code you are referring to.
(2) Say if there is one column of interest (if so, which column) where the word exists, or if the word might exist anywhere in the original (presumably) multi-column list.
(3) Say the column range of the original list; for example, is it A:K? F:R? D:H? Where does this original "copy from" list reside on the source worksheet?
(4) Say the column(s) in the destination worksheet that SHOULD have data copied to it along that row.
(5) Say the column(s) in the destination worksheet that should NOT have data copied to it along that row. Superfluous perhaps, given item (4), but it will help confirm what should go where, and what should not.

 

[jayjayGC]

Hi Tom,

Thanks for getting back to me so quickly.

(1) Post my code you are referring to.

Sub Test2()
Dim myWord$
myWord = InputBox("ROCC Question Search Function", "Enter Key Word")
If myWord = "" Then Exit Sub

Application.ScreenUpdating = False
Dim xRow&, NextRow&, LastRow&
NextRow = 3
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For xRow = 1 To LastRow
If WorksheetFunction.CountIf(Rows(xRow), "*" & myWord & "*") > 0 Then
Rows(xRow).Copy Sheets("Search Results").Rows(NextRow)
NextRow = NextRow + 1
End If
Next xRow
Application.ScreenUpdating = True

MsgBox "Search is Complete, " & NextRow - 3 & " rows containing" & vbCrLf & _
"''" & myWord & "''" & " were copied to Search Results.", 64, "Done"
End Sub

(2) Say if there is one column of interest (if so, which column) where the word exists, or if the word might exist anywhere in the original (presumably) multi-column list.
Yes, column B (or 2) is the one column of interest that will contain the search parameters

(3) Say the column range of the original list; for example, is it A:K? F:R? D:H? Where does this original "copy from" list reside on the source worksheet?
The column range in the original list is A:O on the "Data" sheet (Sheet1)

(4) Say the column(s) in the destination worksheet that SHOULD have data copied to it along that row.

I would like only columns B, C, F, G, H, I, K, M and N to be displayed in the destination worksheet.

(5) Say the column(s) in the destination worksheet that should NOT have data copied to it along that row. Superfluous perhaps, given item (4), but it will help confirm what should go where, and what should not. Columns A, D, E, J, L and O should not be copied to the destination worksheet.


Thank you so much for this - I really do appreciate your expertise and help.

There is one other thing... I may be pushing it now...

Is there a way to have the input box (and therefore button) on a separate sheet so that the users cannot see the actual source sheet? I am using this document as a test for a possible database which will be used by a group of people. Only one person will enter information into the source sheet but everyone will be able to search it and see the results ... I was hoping to design a sheet that has a search input box (and therefore button) that was separate to the data and search results sheets. At present I have placed the button at the top of the data sheet.

Any help you can give me is very much appreciated!

 

[Tom Urtis]

This should do what you want. Put it in a new, fresh module that doesn't have any code in it yet. The Option Compare Text bit is if you (presumably) want to copy (for example) tomato, stomp, and atom if you are searching for Tom.

Assumes your source sheet tab is named Sheet1 and your destination (copy to) worksheet is named Destination. Modify as needed. No particular worksheet needs to be active at the time of macro execution.

Option Compare Text

Sub Test3()

'Declare and define the word being searched for.
Dim myWord$
myWord = InputBox("ROCC Question Search Function", "Enter Key Word")
'Exit the macro if nothing is entered in the InputBox.
If myWord = "" Then Exit Sub

'ScreenUpdating to False.
Application.ScreenUpdating = False

'Declare variable for cell range.
Dim cell As Range
'Declare and define variables for worksheets of interest.
Dim SourceSheet As Worksheet, SearchResults As Worksheet
Set SourceSheet = Worksheets("Sheet1")
Set SearchResults = Worksheets("Destination")
'Declare and define the next available row on the destination sheet.
Dim NextRow&
NextRow = 3

'Open a With structure for the source worksheet.
With SourceSheet
'Loop through each cell with constant data in column B of the source sheet.
For Each cell In .Columns(2).SpecialCells(2)
'If the cell contains the key word, copy its row to the destination sheet.
If InStr(cell.Value, myWord) > 0 Then
.Range(.Cells(cell.Row, 2), .Cells(cell.Row, 14)).Copy SearchResults.Cells(NextRow, 2)
'Clear the cells in the destination sheet that need not be copied.
With SearchResults
.Cells(NextRow, 4).Clear
.Cells(NextRow, 5).Clear
.Cells(NextRow, 10).Clear
.Cells(NextRow, 12).Clear
End With
'Add a 1 to the NextRow valiable to prepare for the next copy action.
NextRow = NextRow + 1
End If
Next cell
'Close the With structure for the source worksheet.
End With

'Set ScreenUpdating to True.
Application.ScreenUpdating = True

'Release system memory holding the worksheet variables.
Set SourceSheet = Nothing
Set SearchResults = Nothing

'Inform the user that the macro is completed.
MsgBox "Search is complete: " & NextRow - 3 & " rows containing" & vbCrLf & _
"''" & myWord & "''" & " were copied.", 64, "Done."

End Sub

 

[jayjayGC]

Thanks Tom, it works perfectly!!

You're a legend

Jen