find value in many sheets...

sal21

Active Member
Joined
Apr 1, 2002
Messages
250
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...
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
try
Code:
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

Active Member
Joined
Apr 1, 2002
Messages
250
try
Code:
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
OK then
try
Code:
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
 

Forum statistics

Threads
1,136,588
Messages
5,676,675
Members
419,643
Latest member
ishanalikhan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top