Find cell locatiopn by cell value in a worksheet or 2D matrix

Hi
this macro finds all instances of a specified word on the worksheet and returns the cell/s where they were found

Please Login or Register  to view this content.

Wonderful guys, thank so much, I really appreciate your help and will give both a try and let you know how it goes this weekend. I am a kind of a man who prefers to know the tool inside out before using it so I will definitely take a look at how macros are written. Isn't it a sort of a script or user procedure? I use to do some coding in BASIC and Pascal back in uni. It might be a time for me to learn macros, never had to use them before that's is also why I thought I'd stick with using functions.

About functions. I was wondering if I could run something like =MATCH("BREAD",$A$1:$Z$1,0) and make it run, like, through every line from 1 to 50 using an array function to find the column number and then run something like =MATCH("BREAD",$A$1:$A$50,0) and run it from A to Z to find the row number and then use the row and the column number to get the coordinates? Or am I overcomplicating things? Why I am taking A to Z 1 to 50 is to make sure I take a big enough area of the table to catch the location of BREAD.

About the example of the formula by bebo021999, I am not too sure I get how it works. What does this expression do: $A$1:$I$12="BREAD" ? ANd then you multiply it by column? Like I said I gotta be lacking brain to just get it. Do you mind explaining how it works? I spent so much time on research so I really want to come to an understanding how it works.

Thanks a lot

Hi folks! I tried the array formula and it worked! I actually had to modify the solution offered by bebo021999 but it is what I was looking for. It is still quite retarded that Excel does not have a built-in feasy feature to search in a matrix but I am able to do it now, thanx! I have not tried the macro yet but I will, it may open new ways of doing what I am doing.

We can close this thread now, I suppose.