Function to search for string across multiple sheets

[chaddug]

This is the first time I have ever tried to write my own function, and I'm not sure what I am doing wrong, but this is not working. I am trying to get a function to search for a value across multiple sheets, and if it finds a match then return the name of the sheet that the value was found on. Here is what I have so far:

Please Login or Register  to view this content.

[Leith Ross]

Hello chaddug,

This version of your macro will return the sheet name where the first occurrence of the text is found. If not then it returns an empty string.

Please Login or Register  to view this content.

[chaddug]

Thanks for the reply, but it is actually not working. I choose to have it search for the first string of text on the first worksheet, and it just returned a blank value. Also, I'm not sure if your code does this already, but it needs to not search the sheet that the function is used on, because the search string will be on that sheet as well as possibly one of the others.

[Leith Ross]

Hello chaddug,

I know the code works because I tested it for posting. The problem maybe that you are not calling the macro correctly. The macro can be called by another VBA macro or used on the worksheet as a function (UDF).

If you post your workbook it will be easier to resolve the issue.

[chaddug]

I figured out why it was not working, it was looking in column A for the match and it should be looking in column C. Thanks for your help!