Find from just certain sheets in a workbook

[Blondegirl]

Hi, I'm a newbie to VBA and am struggling with this bit of coding so I
would really appreciate any help! I need to be able to search just
certain worksheets in the workbook as it's important that the find does
not extend to the other worksheets. The user enters the reference
number in a textbox on "Sheet 1" and it should look through just
certain sheets only and where it finds the matching value, to make that
the active cell. I don't know what I need to add to make my coding work
here. At the moment it won't find any matches and keeps making "sheet
2" the active sheet. (for speed it only needs to search column B, from
B5 onwards, on these sheets)

Private Sub CommandButton2_Click()
On Error Resume Next
Dim Findstring As String
Dim Rng As Range
Dim mysheet As Worksheets
Findstring = TextBox1.Text
Worksheets(Array("Sheet 2", "Sheet 4", _
"Sheet 6", "Sheet 8")).Select

For Each mysheet In Worksheets

If Findstring <> "" Then
Set Rng = Columns(B).Find(What:=Findstring, _
After:=Range("B5"), _
LookIn:=xlValues, _
LookAt:=x1Whole, _
MatchCase:=False).Activate
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "No box found"
TextBox1 = ""
End If
Else
MsgBox "Please enter the box ref"
End If
Next mysheet

End Sub

 

[Jim Cone]

On Error Resume Next should not be used when testing code.
There are several not quite obvious changes:
mysheet as Worksheets to Mysheets as Sheets.
Activate removed
xlWhole had to be retyped.
Columns(B) to Columns("B")
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'---------------
Private Sub CommandButton2_Click()
Dim Findstring As String
Dim Rng As Excel.Range
Dim Mysheets As Excel.Sheets
Dim objSht As Excel.Worksheet

Findstring = InputBox("Enter the box reference", " Blond and Proud")
If Len(Findstring) = 0 Then Exit Sub

Set Mysheets = Worksheets(Array("Sheet 2", "Sheet 4", "Sheet 6", "Sheet 8"))

For Each objSht In Mysheets
'On Error Resume Next
Set Rng = objSht.Columns("B").Find(What:=Findstring, After:=Range("B5"), _
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
'On Error GoTo 0
If Not Rng Is Nothing Then
Application.Goto Rng, True
MsgBox "Found on " & objSht.Name & " in cell " & Rng.Address(False, False) & _
". ", vbInformation, " Blond and Proud"
Exit Sub
End If
Next 'objSht
MsgBox "No box found. ", vbExclamation, " Blond and Proud"
End Sub
'----------


"Blondegirl"wrote in message...
Hi, I'm a newbie to VBA and am struggling with this bit of coding so I
would really appreciate any help! I need to be able to search just
certain worksheets in the workbook as it's important that the find does
not extend to the other worksheets. The user enters the reference
number in a textbox on "Sheet 1" and it should look through just
certain sheets only and where it finds the matching value, to make that
the active cell. I don't know what I need to add to make my coding work
here. At the moment it won't find any matches and keeps making "sheet
2" the active sheet. (for speed it only needs to search column B, from
B5 onwards, on these sheets)

Private Sub CommandButton2_Click()
On Error Resume Next
Dim Findstring As String
Dim Rng As Range
Dim mysheet As Worksheets
Findstring = TextBox1.Text
Worksheets(Array("Sheet 2", "Sheet 4", _
"Sheet 6", "Sheet 8")).Select

For Each mysheet In Worksheets

If Findstring <> "" Then
Set Rng = Columns(B).Find(What:=Findstring, _
After:=Range("B5"), _
LookIn:=xlValues, _
LookAt:=x1Whole, _
MatchCase:=False).Activate
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "No box found"
TextBox1 = ""
End If
Else
MsgBox "Please enter the box ref"
End If
Next mysheet
End Sub

 

[Blondegirl]

Thanks a lot for your help there, but it is bringing up an error which I
can't sort out. It is producing type mismatch (error 13) on the line
'For Each objSht In Mysheets'. Could you tell me what I need to change
to correct it?

 

[Jim Cone]

The code as posted runs for me from a form.
Are your variable declarations the same as I posted?...

Dim Findstring As String
Dim Rng As Excel.Range
Dim Mysheets As Excel.Sheets '<<<not Worksheets
Dim objSht As Excel.Worksheet

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



in message...
Thanks a lot for your help there, but it is bringing up an error which I
can't sort out. It is producing type mismatch (error 13) on the line
'For Each objSht In Mysheets'. Could you tell me what I need to change
to correct it?

 

[Blondegirl]

I've got it working a dream now! I had put 'worksheets' instead of
'worksheet' on the declaration line, Dim objSht As Excel.Worksheet. A
silly error to make. Thanks very much again for your assistance here.