-
Feb 18th, 2012, 05:23 AM
#1
Thread Starter
Junior Member
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
-
Feb 18th, 2012, 05:41 AM
#2
Frenzied Member
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)
-
Feb 18th, 2012, 05:47 AM
#3
Thread Starter
Junior Member
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
-
Feb 18th, 2012, 05:52 AM
#4
Frenzied Member
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
-
Feb 18th, 2012, 06:03 AM
#5
Thread Starter
Junior Member
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
-
Feb 18th, 2012, 06:12 AM
#6
Frenzied Member
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!
-
Feb 18th, 2012, 06:20 AM
#7
Thread Starter
Junior Member
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?
-
Feb 18th, 2012, 06:25 AM
#8
Frenzied Member
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!
-
Feb 18th, 2012, 06:36 AM
#9
Thread Starter
Junior Member
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
-
Feb 18th, 2012, 06:56 AM
#10
Addicted Member
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.
-
Feb 18th, 2012, 07:01 AM
#11
Frenzied Member
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!
-
Feb 18th, 2012, 07:03 AM
#12
Thread Starter
Junior Member
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
-
Feb 18th, 2012, 07:08 AM
#13
Thread Starter
Junior Member
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.
-
Feb 18th, 2012, 07:09 AM
#14
Frenzied Member
Re: Using Find Method Excel VBA
did you just plump for his nibs version?
do you understand how it works?
here to help
-
Jun 4th, 2012, 12:18 AM
#15
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|