VBA search box

[jimmisavage]

A little while ago i asked for help with a code to search a workbook for a reference number i input into a cell.

Thanks for this code guys, i just need 1 little change though...

Private Sub CommandButton1_Click()
Dim rng, sh As Worksheet
Dim WhatToFind As String
Dim Ans As String
WhatToFind = Range("A12").Value ' "InputYourString" ' adjust to your needs ...
For Each sh In ActiveWorkbook.Worksheets
If Not sh.Name = "Front Page" Then
sh.Select
With ActiveSheet
On Error Resume Next
.Columns("B").Select
Selection.Find(what:=WhatToFind, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
If ActiveCell.Row > 6 Then
Ans = MsgBox("Found at " & ActiveCell.Address & " Continue searching ? ", vbYesNo)
If Ans = vbNo Then Exit Sub
End If
End With

I want to seach all the workbook apart from the "Front Page". What do i need to add onto this code?

[bhofsetz]

This code should search all codes EXCEPT the sheet named "Front Page". What problems are you having with it as written?

[mikerickson]

Please Login or Register  to view this content.

It looks like you chopped off the end of some of your code. If the above code finds WhatToFind on a sheet, it will ask you if you want to keep looking. (I wasn't sure what you wanted at that point because the posted code was truncated.) If you say YES, then it will look at the next sheet. This routine will not find two occurances on the same sheet. This can be easily changed.
I also removed some unneeded selecting and such. If you want to go to the found cell after pressing NO, replace the above line with

Please Login or Register  to view this content.