find value in many sheets...

[sal21]

Assuming i have this value USER

and i want to serach the value USER in SHEETa1,SHEETb2,SHEETv3...SHEETc5, and all sheet have the same range for search G2:G5000, now if this value USER is present in one range G2:G50000 of sheets insert in Me.TextBox13.Value = the name of the sheet that contain the value USER...
Possible...

 

[jindon]

try

Sub test()
Dim ws As Worksheet, r As Range
Me.TextBox13.Text = ""
For Each ws In Sheets
   Set r = ws.Range("g2:g50000").Find("USER")
   If Not r Is Nothing Then
      Me.TextBox13.Text = ws.Name
      Exit For
   End If
Next
With Me.TextBox13
   If .Text = "" Then .Text = "Nahhhhh"
End With
End Sub

 

[sal21]

try

Sub test()
Dim ws As Worksheet, r As Range
Me.TextBox13.Text = ""
For Each ws In Sheets
   Set r = ws.Range("g2:g50000").Find("USER")
   If Not r Is Nothing Then
      Me.TextBox13.Text = ws.Name
      Exit For
   End If
Next
With Me.TextBox13
   If .Text = "" Then .Text = "Nahhhhh"
End With
End Sub

HI jindon, tks for code .... you have posted a code to cicling all sheet in wbook. ok, but i spossible to exclude from the find SHEET1 and SHEET3..
Tks.

 

[jindon]

OK then
try

Sub test()
Dim ws As Worksheet, r As Range
Me.TextBox13.Text = ""
For Each ws In Sheets(Array("sheet1","sheet3"))
   Set r = ws.Range("g2:g50000").Find("USER")
   If Not r Is Nothing Then
      Me.TextBox13.Text = ws.Name
      Exit For
   End If
Next
With Me.TextBox13
   If .Text = "" Then .Text = "Nahhhhh"
End With
End Sub