Find text

[colinharwood]

Hi All

I have a workbook which contains 12 sheets, is it possible write a macro which will search for a text string which could be on any of the 12 sheets

Thanks a lot

colin

 

[Joe Was]

Sorry, I cannot reproduce your error?

The code works for me?

Did you add it to a Standard code module, like: Module1?

If you put the code in a Sheet code Module, like: Sheet1, then the code will fail the way you have indicated!

VBA Editor ToolBar: Insert - Module, then paste the code into the module!

 

[MPBJR]

Thanks for the reply,
Yes, I put it in module1. I'm not sure why this was happening. i created a new workbook and re-created the sheets in the new one and it works fine?!? I can't explain it! Thanks again for your help and time!

 

[Joe Was]

In older versions of Office and when we had Net Ware and started to convert to server 2000 we offen had problems like this due to the network lag between the two systems. Now that we have control through one protocol, we do not have this happing. But, once in a wile something goes bust and copying and pasting the sheets to a new Workbook generally fixes the problem?

 

[rebelroy]

Great stuff Joe. Thanks a lot.

 

[tommyb]

Hi guys. Im after something similar!

I've got a spreadsheet (sheet1) that has details of vehicles helpline numbers.

I want to create search box on a seperate spreadsheet, where the user enters a reg number, and it is searched for in sheet1. The row containing the result is then shown under the search box. Is there anyway to do this?

Also, I'd like to lock sheet1 so people dont see anything other than their search criteria. (ideally, I'd like sheet1 to be in a totally separate workbook).

Please help!

 

[Joe Was]

This post should be posted as its own request!
This post is too big to branch out into a data return post as well as a search utility!

Do a post under Excel and I will answer!

 

[Aleciafxc]

Code works great thanks so much. Any ideas for searching fist ## of sheets and not whole workbook. Also, instead of a continue or cancel: first found is made active you do what you need to do and then have an option to "continue" on to the next found. Or search first ## of sheets and at the end of search choose which reference you want to go back to.

Thanks a million for your help.

 

[Joe Was]

I don't know what you mean by: "searching fist ## of sheets?"

And,

option to "continue" on to the next found

has been covered in this post, above!

 

[Aleciafxc]

Instead of searching all sheets or just active sheet, can it be set to search only specified sheets, ie. workbook has 15 sheets where the search value could be found but you only want to search 5 of the 15 total sheets.

When givin the choice to cancel or continue you do not have the option to select the found cell, then when done with data entry move on and continue.

I hope this makes more sense.

 

[Joe Was]

To limit the search to only the sheets you want to search, test for the correct sheets to search names first!

Dim lngMyShts&

For Each ws In ThisWorkbook.Worksheets
lngMyShts = 0
With ws

'These are the names of the sheets to search.
If ws.Name = "xyz1" Then lngMyShts& = 1
If ws.Name = "xyz2" Then lngMyShts& = 1
If ws.Name = "xyz3" Then lngMyShts& = 1

'If the sheet name is not in the list, goto the next sheet!
If lngMyShts <> 1 Then GoTo myNextSht


'Other code here!

myNextSht:
Next ws