MsOf07 Using Find Method Excel VBA-VBForums
Results 1 to 15 of 15

Thread: Using Find Method Excel VBA

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Sep 2011
    Posts
    27

    Using Find Method Excel VBA

    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
    Attached Images Attached Images    

  2. #2
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,197

    Re: Using Find Method Excel VBA

    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)

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Sep 2011
    Posts
    27

    Re: Using Find Method Excel VBA

    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

  4. #4
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,197

    Re: Using Find Method Excel VBA

    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

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Sep 2011
    Posts
    27

    Re: Using Find Method Excel VBA

    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

  6. #6
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,197

    Re: Using Find Method Excel VBA

    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!

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Sep 2011
    Posts
    27

    Re: Using Find Method Excel VBA

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

  8. #8
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,197

    Re: Using Find Method Excel VBA

    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!

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Sep 2011
    Posts
    27

    Re: Using Find Method Excel VBA

    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

  10. #10
    Addicted Member
    Join Date
    Jul 2009
    Posts
    208

    Re: Using Find Method Excel VBA

    Try something like this:
    Code:
    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.

  11. #11
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,197

    Re: Using Find Method Excel VBA

    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!

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Sep 2011
    Posts
    27

    Re: Using Find Method Excel VBA

    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

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Sep 2011
    Posts
    27

    Re: Using Find Method Excel VBA

    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.

  14. #14
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,197

    Re: Using Find Method Excel VBA

    did you just plump for his nibs version?

    do you understand how it works?

    here to help

  15. #15
    New Member
    Join Date
    Jun 2012
    Posts
    1

    Re: Using Find Method Excel VBA

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.