Using Find Method Excel VBA

[Andreew]

Hi,

I have developed a movie list to store information about my movies (movie name, date added, location etc.)

I am using a simple Find method to seach for a movie in the list. The movie names are located in column A and the movies' binder (location of movie) is stored in column D. When i search for a movie i have the results transferred to another sheet for easy veiwing. So far i can search for a movie and view all movies containing that phrase in the new sheet but i cannot work out how to also search/view the corresponding binder for each movie and have that copied across also, any help would be greatly appreciated. I have included some pictures to better explain what i can't lol.

Capture 1 = Sheet that stores all the movie information
Capture 2 = Current code i am using as Find Method
Capture 3 = Sheet that search results are copied to

[incidentals]

all you need for this is a vlookup

but you need to bild your rows in the output sheet with the result of 2 such results

the column you have returned and a 2nd column that gets the vlookup value using the first columns value

hope that helps (code if needed)

[Andreew]

Hi,

thanks for the fast reply. yes i'm not 100% sure i understand what you mean, if you had some sample code that would be great.

Thanks,

Andrew

[incidentals]

question what do you collect when you collect the names of the things found?

do you ever see the address of the cell or have some objecty thing that appears to you to be the cell?

if you can say yes to these questions i gan suggest 1 answer

if you say no i will suggest another answer, but that too will require another question!

here to help

[Andreew]

When i use the Find method it simply searches Column A of the first sheet and copies any movie with the words or phrase in column A to column A of the second sheet, you don't see the address of the cell or and oject thing. so im not sure that anything is really collected just merely copied or viewed in a different sheet, if that answers your questions :L.

Thanks for the help, much appreciated,

Andrew

[incidentals]

ok can you add "hello" to the cell next to the one being added to by your process?

if you can then you are halfway there!

[Andreew]

Yes i can add hello to the cell being added; this would be B6 if you refer to the third picture i added?

[incidentals]

ok so you can add anything into this cell?

so why not add "=a6"

you know the row and you know the col so you can makeup the cell name or use cols(r,c)

go and try - and hurry back now!

[Andreew]

adding "=a6" duplicated the results i had in "a6", not quite what i'm looking for. Just to clarify on the main sheet i have ("Edit MASTER Only") i have the names of serval different movies (A column) and there locations or 'binder' (D Column) when i search for a movie using the search button i have on the list it searches column A for any cell with the word i search in it and copies each cell that has that word in it to another sheet called "OUTPUT Folder" starting at A6. When i search i want to be able to not only see the name of the movie in column A of the output sheet but the binder of the movie in column b aswell. for example if i search for "007" it will bring up any movie with "007" in it but it will not show the binder for each movie (each movie entry has a diffrent binder number - 1st picture) thats what i need to be able to do.

thanks,

andrew

[His Nibbs]

Try something like this:

Worksheets("OUTPUT FOLDER").Cells(rowCount,2).Value = Worksheets("Edit MASTER Only").Cells(c.Row,"D").Value

PS - It's easier for people to help you if you post code as text inside [code ] tags, rather than as a screen image.

[incidentals]

I Know what you want!

This site is about you getting the knowledge not just the answer.

sometimes when I take someone through the process they jump off an comlete the process once they have been nudged in the right direction!

The point of doing the exercise is that it helps you build the solution in memorable and meaninfull way!

WHAT YOU CAN DO IS BUILD A SOLUTION THAT ADDS A FUNCTION TO A SHEET!!

so the next question is ( and we are nearly at the solution! honest!!)

can you identify the row value for your newly created bit?

so you could have put "=a" & rowvalue

if yes then the next bit is the final solution!

[Andreew]

Hi,

Yes that worked perfectly, thanks. Next time i will post the code like that, I havent really used this site much so i don't really know the best way to do things.

Thanks heaps,

Andrew

[Andreew]

incidentals,

i didn't mean to seem ungrateful, i just wasnt sure if i had made sense in the original post. To answer you questions yes i can identify the row value.

[incidentals]

did you just plump for his nibs version?

do you understand how it works?

here to help

[ramkishoragrawal]

I create a movie database in ms access 2003. If you are intrusting ms access 2003 movie database, contact me.