Search ALL sheets

[isildad]

Here is the code I try to adapt to search in ALL sheets and not just Sheet1 (Code is in a userform)

How do you change Sheet.("Sheet1") to "All Sheets" ?
Thank you for your help

Option Explicit

Private Sub CommandButton1_Click()
Dim rngfind As Range
With Sheets("sheet1")
Set rngfind = .Range("B:B").Find(Me.TextBox1.Value, lookat:=xlWhole, MatchCase:=True)
If rngfind Is Nothing Then
MsgBox "Not Found"
Else
Me.TextBox2.Value = rngfind.Offset(0, 1).Value
Me.TextBox3.Value = rngfind.Offset(0, 2).Value
End If
End With
End Sub

 

[Doug.T]

Private Sub CommandButton1_Click()
Dim rngfind As Range

For each sh in activeworkbook.sheets

With Sheets(sh.name)
Set rngfind = .Range("B:B").Find(Me.TextBox1.Value, lookat:=xlWhole, MatchCase:=True)
If rngfind Is Nothing Then
MsgBox "Not Found"
Else
Me.TextBox2.Value = rngfind.Offset(0, 1).Value
Me.TextBox3.Value = rngfind.Offset(0, 2).Value
End If
End With

next sh

End Sub

 

[isildad]

Thank you , have tried the code and get "Compile error Variable not defined with the sh highlighted in: For Each sh In ActiveWorkbook.Sheets

 

[isildad]

Anyone else ?

 

[tusharm]

See
Enhanced Find
http://www.tushar-mehta.com/excel/software/utilities/enhanced_find/index.html

 

[pgc01]

Hi isildad

Variable not defined with the sh highlighted

If you know sh is not defined why don't you define it?

Since sh is a worksheet, add it to the declarations

Dim rngfind As Range, sh as Worksheet

HTH
PGC

 

[isildad]

Thank you for the replies
Here is the code I now have.
It works in finding in either sheets but in spite of it the warning "not found" comes up every time.

Option Explicit

Private Sub CommandButton1_Click()

Dim rngfind As Range, sh As Worksheet
For Each sh In ActiveWorkbook.Sheets

With Sheets(sh.Name)
Set rngfind = .Range("B:B").Find(Me.TextBox1.Value, lookat:=xlWhole, MatchCase:=True)
If rngfind Is Nothing Then
MsgBox "Not Found"
Else
Me.TextBox2.Value = rngfind.Offset(0, 1).Value
Me.TextBox3.Value = rngfind.Offset(0, 2).Value
End If
End With

Next sh

End Sub

 

[jindon]

try

Option Explicit

Private Sub CommandButton1_Click()

Dim rngfind As Range, sh As Worksheet
For Each sh In ActiveWorkbook.Sheets

With Sheets(sh.Name)
Set rngfind = .Range("B:B").Find(Me.TextBox1.Value, lookat:=xlWhole, MatchCase:=True)
If Not rngfind Is Nothing Then
Me.TextBox2.Value = rngfind.Offset(0, 1).Value
Me.TextBox3.Value = rngfind.Offset(0, 2).Value
Exit Sub
End If
End With

Next sh
MsgBox "Not Found"
End Sub

 

[isildad]

Thank you Jindon
Works like it should