find value in many sheets...

sal21

Active Member
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

jindon

MrExcel MVP
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
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
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``````

Replies
2
Views
716
Replies
1
Views
148
Replies
0
Views
75
Replies
1
Views
217
Replies
5
Views
662

1,141,417
Messages
5,706,311
Members
421,441
Latest member
VapesRub

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.

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

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