search for keywords within many words in a cell

[dan2010]

Hi,

i am trying to figure out the coding to search for different keywords, then copy and paste them into another sheet in different columns.

eg.

cell 1A :
john
banana
$1.2

cell 2A :
kelvin
durian
$2.4

cell 3A :
kenny
orange
$1.5

cell 1B:
kenny
durian
$1.3

cell 2B:
john
banana
$1.2

the list goes on....from 1A to 30D

all these data are in sheet1. My plan is to sort these data into sheet2.
those data with john goes to 1st column, those with kelvin goes to column 2, those with kenny goes to 3rd column...and so on

i have tried using wildcard, but nothing happens.

Please Login or Register  to view this content.

does anyone has any idea how to solve my problem? thanks =D

[StephenR]

Untested, but does this work?

Please Login or Register  to view this content.

[Marcol]

Try this assuming you have set your range and variables a, b, c

Please Login or Register  to view this content.

Hope this helps

[snb]

Use Excel's Builtin facilities.

Please Login or Register  to view this content.

[dan2010]

StephenR : the code is not valid

Marcol : i tried your coding but nothing happens.

snb : it returns runtime error 1004. " The command could not be completed by using the range specified.Select a single cell within the range and try the command again."

[dan2010]

Please Login or Register  to view this content.

i have tried this code but it does not work too. it always return k = 0. I have tried to use "john","kelvin","kenny" and it works. So is there any replacement coding for "*john*","*kelvin*","*kenny*" ?

[Marcol]

My mistake try this

Please Login or Register  to view this content.

If this still does nothing, or doesn't give you the result you need, post a sample workbook.

[snb]

Please Login or Register  to view this content.

It's imperative that A1 serves as a 'Fieldname'.
Cfr. the attachment

[dan2010]

do u mind explaining

Please Login or Register  to view this content.

why is cell D1 = A1? when the data are copied to column H,J,L

and

Please Login or Register  to view this content.

there is nothing in cell D2.

and

Please Login or Register  to view this content.

i do not understand the entire line of the code :S

[snb]

the selectioncriteria for advanced filter will be put into D1:D2

.usedrange.Columns(1).AdvancedFilter xlFilterCopy, .Range("D1:D2"), .Cells(1, 10 + (2 * j))

the selectioncriteria indicate which field has to be filtered
so D1 has to contain the same fieldname as the column to be filtered.
that column's fieldname is in A1
ergo put the fieldname of column 1 (A1) in the fieldselectorcriterion D1
.Cells(1, 4) = .Cells(1, 1)

put in D2 the value that has to be filtered in column 1
.Cells(2, 4) = Choose(j, "*John*", "*Kelvin*", "*Kenny*")

If you want to know what advancedfilter is :
I can't explain better than F1, a good Excel-VBA-book, an Internet Excelcourse or a classroom Excel-VBA course can.

[Leith Ross]

Hello dan2010,

When asking for explanations of code, you should also state if the code worked or not. If not, you should include the line where the failure occurred, and the number of the error. If it does work and you have no further changes that need to be made then please marked your post solved and thank those who helped you.

[dan2010]

snb:

Please Login or Register  to view this content.

i assume this code is only for data in column A. how can i change it to search from B2 to E32? 2nd question, does fieldname refer to "*john*","*kelvin*","*kenny*"?

Thank you for your explanation.

Leith Ross: the code provided by snb is working but i do not understand how to modify the code to suit my needs, that's why i have not marked the post as solved. rest assured that i will do it when i have clarified all my doubts. sorry for the trouble.

[dan2010]

i have copied all the data into column "z" in sheet("hidden2") and set the fieldname and criteria in cell y1 and y2. the problem is that i have 129 cells of raw data in column z but only around 80 cells are copied to column a to i. do u have any idea what is wrong with my coding? and is it possible to remove the fieldname? thank you.

Please Login or Register  to view this content.

[StephenR]

Dan - I suggest you post a sample workbook and I'm sure we can sort it out in no time.

[snb]

If the range you want to inspect is B1:E32.

Please Login or Register  to view this content.

[dan2010]

snb : i see. thank you. i will keep ur code for reference.

StephenR : it is okay. i have found another "easier to understand" solution for my problem, using "if range.value like "*variable*" then..."

i would like to thank StephenR, Marcol and especially snb for all the time and effort spent giving me all the help and suggestions. thank you very much =D