Finding words in Excel

September 19, 2011 at 03:35:58
Specs: Windows XP
This may be very a very simple Q for you guys, but I have no idea.

My background is programming in SAS so excel seems to be chinese but I need help with this if possible.

What I am trying to do.

On sheet 1 I have 3 columns, Date, Dealer, Concern.

I'd like to by hitting a button, pick out a number key words from the 'concern' list and have them listed on sheet 2.

for example, I can have up to 20 rows of info in sheet 1, if in the 'concern' column the words 'broken' or 'damaged' came up (the box does have a string of text and 'broken' would possibly be one of the words), I'd like that row of info on sheet 2.

Is this possible?

Any help would be very much appreciated.

See More: Finding words in Excel

Report •

September 19, 2011 at 04:53:16
OK, using one of the examples from this site, i have managed to find something very useful...which is:

Option Explicit
Sub wordFinder()
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
'Determine length of Search Column from Sheet3
srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column K, copy it top the next row in Sheet2
With Sheets(1).Columns("K")
For gName = 2 To srchLen
Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole)
If Not g Is Nothing Then
nxtRw = Sheets(2).Range("K" & Rows.Count).End(xlUp).Row + 1
g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
End If
End With
End Sub

it works a treat if in my K column (my 'Concern') was one worded, to either 'broken' or 'damaged'.

I need to be able to search a sentance in K for the keyword. for examlpe if in my K column I had "There was a broken glass"

Report •
Related Solutions

Ask Question