Find, highlight, mark, cut and paste macro

[Craig Freeman]

Hello,

I'm hoping that what I want to do is possible, as it would save me a
great deal of time.

I have a worksheet, we can call it 'data', that contains several
thousand rows of data. What I'd like to do, is allow the user to enter
their text string in an inputbox, search entire datasheet (excluding
header a1), and when a match is found, for that entire row to be
highlighted (yellow), and an "yes" indicator to be place in the last
column of datasheet - say column 'f'. (doing nothing if no match is
made)

As the final step, I'd like at the same time for that row (and if
possible the row number) to copied to a separate 'results' sheet
(allowing for header a1), and place the users search string in column
'g'. Any subsequent searches would need to append to the results sheet
(ie. leave data from previous search).

I got the folloing code from a previous post, but only does half of
what I want it to do.

Any help is greatly appreciated.

Sub FindValueAndCopy()

On Error GoTo HANDLEERROR

'** prompts user on what to find
Prompt = "What do you want to find ?"
Title = "Find"
ValueToFind = InputBox(Prompt, Title)
If ValueToFind = "" Then End


' can name sheet what ever you want
' ** MAKE SURE YOU HAVE A SHEET WITH THIS NAME **
SheetToCopyTo = "Values Found"


TotalNumberOfSheet = Sheets.Count
NumFound = 0
For s = 1 To TotalNumberOfSheet
'** scrolls through Sheet by Sheet
Sheets(s).Select
If ActiveSheet.Name = SheetToCopyTo Then GoTo SKIP


'** Searches for value entered
Set Search = Cells.Find(What:=ValueToFind, _
LookIn:=xlValue)
If Search Is Nothing _
Then
Message = ValueToFind & " was NOT found on " & _
ActiveSheet.Name
m = MsgBox(messgae, vbInformation, "Not Found")
Else
FirstFoundAddress = Search.Address
Do
' highlights Entire row as color Yellow
NumFound = NumFound + 1
Rows(Search.Row).Select
With Selection.Interior
.ColorIndex = 6 ' 6 = yellow
.Pattern = xlSolid
End With
' copies entire row to default sheet to
' copy to
Selection.Copy
Sheets(SheetToCopyTo).Select
Cells(NumFound, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets(s).Select
' finds next value
Set Search = Cells.FindNext(Search)
Loop While Not (Search Is Nothing) And Search.Address <>
FirstFoundAddress
End If
SKIP:
Next s


'***** Handles Errors *****
Exit Sub
HANDLEERROR: ErrorMessage = "ERROR " & Err.Number & " - " &
Err.Description
m = MsgBox(ErrorMessage, vbCritical, "Error")
Err.Clear
End


End Sub

[Craig Freeman]

Hello,

Sorry, I need to refine my requirements a little...I no longer require
for the entire row to be highlighted (yellow), and an "yes" indicator
to be place in the last column of datasheet. I only require the row
for the match string to be copied and appended to results sheet, along
with copying the search string itself to column 'g' of the results
sheet. Additionally, if I could have the user specify which column to
search in the data sheet instead of searching the entire row...that
would be great.

Is this possible?

Craig

[Craig Freeman]

budabump...anyone?

[T-容x]

Hi Craig! Try this...

Put the following in a module...

Please Login or Register  to view this content.

budabump...anyone?

[Craig Freeman]

Wow...that's great great T-=AEex! It works perfectly. I can't thank you
enough.

Take care,
..=2E.thanks again
Craig

[Craig Freeman]

opps...Is there way to modify this to only search for whole words.?

[T-容x]

opps...Is there way to modify this to only search for whole words.?

Hi Craig! Sorry for the delay... I was away... Anywayz, look for the line

Find(SearchStr, LookIn:=xlValues)

in the code (in sub FindAndCopy). This is what searches for the values. Just replace it with:

Find(SearchStr, LookIn:=xlValues, LookAt:=xlWhole)

if you want to match whole words, or:

Find(SearchStr, LookIn:=xlValues, LookAt:=xlPart)

if you want partial matching.

This is the syntax for Find (from VBA Help):

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

What - the value you're looking for

After - the cell after w/c you want the search to begin

LookIn - set to xlValues if you want to search in the values (what's displayed); set to xlFormulas if you want to search in the formulas. (I'm not sure, but there probably are other options.)

LookAt - set to xlWhole to match whole words; set to xlPart for partial matching

MatchCase - set to True if you want the search to be case-sensitive; set to False for case-insensitive

[Craig Freeman]

Hey T-=AEex,

Really no delay at all...

The problem I was having with 'xlwhole' was that if there were two or
more whole words in the cell, the function would return no results,
even if there was a match with one of the whole words. So if I was
searching for 'apple', and 'candy apple' was in the cell, no match was
made. What I want to eliminate, is a search for 'andy' returning
'candy'.

Any ideas?

Cheers,
Craig

[T-容x]

Hi Craig! You can actually use wildcard characters... Here's one way I can think of.
In the previous code, replace the sub FindAndCopy with the new FindAndCopy below and add the new function MatchWhole.

Please Login or Register  to view this content.

Hope this helps...

Hey T-=AEex,

Really no delay at all...

The problem I was having with 'xlwhole' was that if there were two or
more whole words in the cell, the function would return no results,
even if there was a match with one of the whole words. So if I was
searching for 'apple', and 'candy apple' was in the cell, no match was
made. What I want to eliminate, is a search for 'andy' returning
'candy'.

Any ideas?

Cheers,
Craig

[Craig Freeman]

Great! I appreciate all your help.

take care,
Craig