Starting in the first worksheet (Sheet00.Name), I wish to search all visible
worksheets for a value (value of the active cell).
(The user of the spreadsheet will click a command button for this VBA to run)
[I have turned off the On Error instruction, while testing this out...]

Private Sub FindCellContent()

'On Error GoTo addError1

Dim CellLocation As Range
Dim CellContent As String
Dim Sh As Worksheet

' Capture cell information
Set CellLocation = ActiveCell
CellContent = ActiveCell

' Start at the first Worksheet
Sheets(Sheet31.Name).Select
Range("A1").Select

' Search for CellContent in all Visible Worksheets
For Each Sh In ThisWorkbook.Worksheets
' If Sh.Visible = xlSheetVisible Then
If Cells.Find(What:=CellContent, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then
Exit For
End If
' End If
Next


'addError1:

' Application.GoTo Reference:=CellLocation
' MsgBox "Cell content not found in other worksheets"
' Exit Sub

End Sub

Thank you... Clive

If you're using a commandbutton from the control toolbox toolbar, then the code
will be behind the worksheet with that commandbutton. (You don't need to call a
separate routine in a General module.) But you are tied into the name of the
procedure. It'll be the commandbutton's name_Click:

Option Explicit
Private Sub CommandButton1_Click()

Dim myVal As Variant 'long, string, double???
Dim wks As Worksheet
Dim FoundCell As Range

myVal = ActiveCell.Value


For Each wks In Me.Parent.Worksheets
With wks
If Me.Name = wks.Name Then
'skip this sheet
Else
If .Visible = xlSheetVisible Then
Set FoundCell = .Cells.Find(What:=myVal, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundCell Is Nothing Then
MsgBox myVal & " was NOT found on " & .Name
Else
MsgBox myVal & " was found on " & .Name _
& " in " & FoundCell.Address(0, 0)
'go there and stop looking?
'Application.Goto FoundCell, Scroll:=True
'Exit For
End If
End If
End If
End With
Next wks

End Sub

By using a variable (FoundCell), you can avoid the .activate error. Then just
check to see if the value was found (with "if foundcell is nothing").



RiverGully wrote:

Starting in the first worksheet (Sheet00.Name), I wish to search all visible
worksheets for a value (value of the active cell).
(The user of the spreadsheet will click a command button for this VBA to run)
[I have turned off the On Error instruction, while testing this out...]

Private Sub FindCellContent()

'On Error GoTo addError1

Dim CellLocation As Range
Dim CellContent As String
Dim Sh As Worksheet

' Capture cell information
Set CellLocation = ActiveCell
CellContent = ActiveCell

' Start at the first Worksheet
Sheets(Sheet31.Name).Select
Range("A1").Select

' Search for CellContent in all Visible Worksheets
For Each Sh In ThisWorkbook.Worksheets
' If Sh.Visible = xlSheetVisible Then
If Cells.Find(What:=CellContent, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then
Exit For
End If
' End If
Next


'addError1:

' Application.GoTo Reference:=CellLocation
' MsgBox "Cell content not found in other worksheets"
' Exit Sub

End Sub

Thank you... Clive

--

Dave Peterson

Thank you...
With the script I am getting the following message/error:

Compile Error: Invalid use of the Me keyword
For Each wks In Me.Parent.Worksheets

Can you assist further please... Many thanks.

Clive


"Dave Peterson" wrote:

If you're using a commandbutton from the control toolbox toolbar, then the code
will be behind the worksheet with that commandbutton. (You don't need to call a
separate routine in a General module.) But you are tied into the name of the
procedure. It'll be the commandbutton's name_Click:

Option Explicit
Private Sub CommandButton1_Click()

Dim myVal As Variant 'long, string, double???
Dim wks As Worksheet
Dim FoundCell As Range

myVal = ActiveCell.Value


For Each wks In Me.Parent.Worksheets
With wks
If Me.Name = wks.Name Then
'skip this sheet
Else
If .Visible = xlSheetVisible Then
Set FoundCell = .Cells.Find(What:=myVal, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundCell Is Nothing Then
MsgBox myVal & " was NOT found on " & .Name
Else
MsgBox myVal & " was found on " & .Name _
& " in " & FoundCell.Address(0, 0)
'go there and stop looking?
'Application.Goto FoundCell, Scroll:=True
'Exit For
End If
End If
End If
End With
Next wks

End Sub

By using a variable (FoundCell), you can avoid the .activate error. Then just
check to see if the value was found (with "if foundcell is nothing").



RiverGully wrote:

Starting in the first worksheet (Sheet00.Name), I wish to search all visible
worksheets for a value (value of the active cell).
(The user of the spreadsheet will click a command button for this VBA to run)
[I have turned off the On Error instruction, while testing this out...]

Private Sub FindCellContent()

'On Error GoTo addError1

Dim CellLocation As Range
Dim CellContent As String
Dim Sh As Worksheet

' Capture cell information
Set CellLocation = ActiveCell
CellContent = ActiveCell

' Start at the first Worksheet
Sheets(Sheet31.Name).Select
Range("A1").Select

' Search for CellContent in all Visible Worksheets
For Each Sh In ThisWorkbook.Worksheets
' If Sh.Visible = xlSheetVisible Then
If Cells.Find(What:=CellContent, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then
Exit For
End If
' End If
Next


'addError1:

' Application.GoTo Reference:=CellLocation
' MsgBox "Cell content not found in other worksheets"
' Exit Sub

End Sub

Thank you... Clive

--

Dave Peterson

Try the below

Sub FindCellContent()

Dim Sh As Worksheet, foundCell As Range

'Search for CellContent in all Visible Worksheets
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = xlSheetVisible Then

'If you want to ignore the active sheet unmark the below line
'If ActiveSheet.Name < Sh.Name Then

Set foundCell = Sh.Cells.Find(What:=ActiveCell, _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If Not foundCell Is Nothing Then
If foundCell.Address(External:=True) < _
ActiveCell.Address(External:=True) Then _
Application.Goto foundCell: Exit For
End If

'/If you want to ignore the active sheet
'End If

End If
Next

If foundCell Is Nothing Then _
MsgBox ActiveCell & " not found in this workbook"

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"RiverGully" wrote:

Thank you...
With the script I am getting the following message/error:

Compile Error: Invalid use of the Me keyword
For Each wks In Me.Parent.Worksheets

Can you assist further please... Many thanks.

Clive


"Dave Peterson" wrote:

If you're using a commandbutton from the control toolbox toolbar, then the code
will be behind the worksheet with that commandbutton. (You don't need to call a
separate routine in a General module.) But you are tied into the name of the
procedure. It'll be the commandbutton's name_Click:

Option Explicit
Private Sub CommandButton1_Click()

Dim myVal As Variant 'long, string, double???
Dim wks As Worksheet
Dim FoundCell As Range

myVal = ActiveCell.Value


For Each wks In Me.Parent.Worksheets
With wks
If Me.Name = wks.Name Then
'skip this sheet
Else
If .Visible = xlSheetVisible Then
Set FoundCell = .Cells.Find(What:=myVal, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundCell Is Nothing Then
MsgBox myVal & " was NOT found on " & .Name
Else
MsgBox myVal & " was found on " & .Name _
& " in " & FoundCell.Address(0, 0)
'go there and stop looking?
'Application.Goto FoundCell, Scroll:=True
'Exit For
End If
End If
End If
End With
Next wks

End Sub

By using a variable (FoundCell), you can avoid the .activate error. Then just
check to see if the value was found (with "if foundcell is nothing").



RiverGully wrote:

Starting in the first worksheet (Sheet00.Name), I wish to search all visible
worksheets for a value (value of the active cell).
(The user of the spreadsheet will click a command button for this VBA to run)
[I have turned off the On Error instruction, while testing this out...]

Private Sub FindCellContent()

'On Error GoTo addError1

Dim CellLocation As Range
Dim CellContent As String
Dim Sh As Worksheet

' Capture cell information
Set CellLocation = ActiveCell
CellContent = ActiveCell

' Start at the first Worksheet
Sheets(Sheet31.Name).Select
Range("A1").Select

' Search for CellContent in all Visible Worksheets
For Each Sh In ThisWorkbook.Worksheets
' If Sh.Visible = xlSheetVisible Then
If Cells.Find(What:=CellContent, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then
Exit For
End If
' End If
Next


'addError1:

' Application.GoTo Reference:=CellLocation
' MsgBox "Cell content not found in other worksheets"
' Exit Sub

End Sub

Thank you... Clive

--

Dave Peterson

Hi Jacob,

The error message is regarding a missing NEXT statement in response to your
starting off with stating FOR Each Sh in ActiveWorkbook.Worksheets

Could you recheck for me.

Many Thanks.... Clive


"Jacob Skaria" wrote:

Try the below

Sub FindCellContent()

Dim Sh As Worksheet, foundCell As Range

'Search for CellContent in all Visible Worksheets
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = xlSheetVisible Then

'If you want to ignore the active sheet unmark the below line
'If ActiveSheet.Name < Sh.Name Then

Set foundCell = Sh.Cells.Find(What:=ActiveCell, _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If Not foundCell Is Nothing Then
If foundCell.Address(External:=True) < _
ActiveCell.Address(External:=True) Then _
Application.Goto foundCell: Exit For
End If

'/If you want to ignore the active sheet
'End If

End If
Next

If foundCell Is Nothing Then _
MsgBox ActiveCell & " not found in this workbook"

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"RiverGully" wrote:

Thank you...
With the script I am getting the following message/error:

Compile Error: Invalid use of the Me keyword
For Each wks In Me.Parent.Worksheets

Can you assist further please... Many thanks.

Clive


"Dave Peterson" wrote:

If you're using a commandbutton from the control toolbox toolbar, then the code
will be behind the worksheet with that commandbutton. (You don't need to call a
separate routine in a General module.) But you are tied into the name of the
procedure. It'll be the commandbutton's name_Click:

Option Explicit
Private Sub CommandButton1_Click()

Dim myVal As Variant 'long, string, double???
Dim wks As Worksheet
Dim FoundCell As Range

myVal = ActiveCell.Value


For Each wks In Me.Parent.Worksheets
With wks
If Me.Name = wks.Name Then
'skip this sheet
Else
If .Visible = xlSheetVisible Then
Set FoundCell = .Cells.Find(What:=myVal, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundCell Is Nothing Then
MsgBox myVal & " was NOT found on " & .Name
Else
MsgBox myVal & " was found on " & .Name _
& " in " & FoundCell.Address(0, 0)
'go there and stop looking?
'Application.Goto FoundCell, Scroll:=True
'Exit For
End If
End If
End If
End With
Next wks

End Sub

By using a variable (FoundCell), you can avoid the .activate error. Then just
check to see if the value was found (with "if foundcell is nothing").



RiverGully wrote:

Starting in the first worksheet (Sheet00.Name), I wish to search all visible
worksheets for a value (value of the active cell).
(The user of the spreadsheet will click a command button for this VBA to run)
[I have turned off the On Error instruction, while testing this out...]

Private Sub FindCellContent()

'On Error GoTo addError1

Dim CellLocation As Range
Dim CellContent As String
Dim Sh As Worksheet

' Capture cell information
Set CellLocation = ActiveCell
CellContent = ActiveCell

' Start at the first Worksheet
Sheets(Sheet31.Name).Select
Range("A1").Select

' Search for CellContent in all Visible Worksheets
For Each Sh In ThisWorkbook.Worksheets
' If Sh.Visible = xlSheetVisible Then
If Cells.Find(What:=CellContent, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then
Exit For
End If
' End If
Next


'addError1:

' Application.GoTo Reference:=CellLocation
' MsgBox "Cell content not found in other worksheets"
' Exit Sub

End Sub

Thank you... Clive

--

Dave Peterson

I have tested this and is working for me. Have you commented any IF
statements. Copy and paste the below in full and try;


Sub FindCellContent()

Dim Sh As Worksheet, foundCell As Range

'Search for CellContent in all Visible Worksheets
For Each Sh In ActiveWorkbook.Worksheets
'If Sh.Visible = xlSheetVisible Then

If ActiveSheet.Name < Sh.Name Then

Set foundCell = Sh.Cells.Find(What:=ActiveCell, _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If Not foundCell Is Nothing Then
If foundCell.Address(External:=True) < _
ActiveCell.Address(External:=True) Then _
Application.Goto foundCell: Exit For
End If

End If

'End If
Next

If foundCell Is Nothing Then _
MsgBox ActiveCell & " not found in this workbook"

End Sub



If this post helps click Yes
---------------
Jacob Skaria


"RiverGully" wrote:

Hi Jacob,

The error message is regarding a missing NEXT statement in response to your
starting off with stating FOR Each Sh in ActiveWorkbook.Worksheets

Could you recheck for me.

Many Thanks.... Clive


"Jacob Skaria" wrote:

Try the below

Sub FindCellContent()

Dim Sh As Worksheet, foundCell As Range

'Search for CellContent in all Visible Worksheets
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = xlSheetVisible Then

'If you want to ignore the active sheet unmark the below line
'If ActiveSheet.Name < Sh.Name Then

Set foundCell = Sh.Cells.Find(What:=ActiveCell, _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If Not foundCell Is Nothing Then
If foundCell.Address(External:=True) < _
ActiveCell.Address(External:=True) Then _
Application.Goto foundCell: Exit For
End If

'/If you want to ignore the active sheet
'End If

End If
Next

If foundCell Is Nothing Then _
MsgBox ActiveCell & " not found in this workbook"

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"RiverGully" wrote:

Thank you...
With the script I am getting the following message/error:

Compile Error: Invalid use of the Me keyword
For Each wks In Me.Parent.Worksheets

Can you assist further please... Many thanks.

Clive


"Dave Peterson" wrote:

If you're using a commandbutton from the control toolbox toolbar, then the code
will be behind the worksheet with that commandbutton. (You don't need to call a
separate routine in a General module.) But you are tied into the name of the
procedure. It'll be the commandbutton's name_Click:

Option Explicit
Private Sub CommandButton1_Click()

Dim myVal As Variant 'long, string, double???
Dim wks As Worksheet
Dim FoundCell As Range

myVal = ActiveCell.Value


For Each wks In Me.Parent.Worksheets
With wks
If Me.Name = wks.Name Then
'skip this sheet
Else
If .Visible = xlSheetVisible Then
Set FoundCell = .Cells.Find(What:=myVal, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundCell Is Nothing Then
MsgBox myVal & " was NOT found on " & .Name
Else
MsgBox myVal & " was found on " & .Name _
& " in " & FoundCell.Address(0, 0)
'go there and stop looking?
'Application.Goto FoundCell, Scroll:=True
'Exit For
End If
End If
End If
End With
Next wks

End Sub

By using a variable (FoundCell), you can avoid the .activate error. Then just
check to see if the value was found (with "if foundcell is nothing").



RiverGully wrote:

Starting in the first worksheet (Sheet00.Name), I wish to search all visible
worksheets for a value (value of the active cell).
(The user of the spreadsheet will click a command button for this VBA to run)
[I have turned off the On Error instruction, while testing this out...]

Private Sub FindCellContent()

'On Error GoTo addError1

Dim CellLocation As Range
Dim CellContent As String
Dim Sh As Worksheet

' Capture cell information
Set CellLocation = ActiveCell
CellContent = ActiveCell

' Start at the first Worksheet
Sheets(Sheet31.Name).Select
Range("A1").Select

' Search for CellContent in all Visible Worksheets
For Each Sh In ThisWorkbook.Worksheets
' If Sh.Visible = xlSheetVisible Then
If Cells.Find(What:=CellContent, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then
Exit For
End If
' End If
Next


'addError1:

' Application.GoTo Reference:=CellLocation
' MsgBox "Cell content not found in other worksheets"
' Exit Sub

End Sub

Thank you... Clive

--

Dave Peterson

If you're using a commandbutton from the control toolbox toolbar, then the code
will be behind the worksheet with that commandbutton. (You don't need to call a
separate routine in a General module.) But you are tied into the name of the
procedure. It'll be the commandbutton's name_Click:



RiverGully wrote:

Thank you...
With the script I am getting the following message/error:

Compile Error: Invalid use of the Me keyword
For Each wks In Me.Parent.Worksheets

Can you assist further please... Many thanks.

Clive

"Dave Peterson" wrote:

If you're using a commandbutton from the control toolbox toolbar, then the code
will be behind the worksheet with that commandbutton. (You don't need to call a
separate routine in a General module.) But you are tied into the name of the
procedure. It'll be the commandbutton's name_Click:

Option Explicit
Private Sub CommandButton1_Click()

Dim myVal As Variant 'long, string, double???
Dim wks As Worksheet
Dim FoundCell As Range

myVal = ActiveCell.Value


For Each wks In Me.Parent.Worksheets
With wks
If Me.Name = wks.Name Then
'skip this sheet
Else
If .Visible = xlSheetVisible Then
Set FoundCell = .Cells.Find(What:=myVal, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundCell Is Nothing Then
MsgBox myVal & " was NOT found on " & .Name
Else
MsgBox myVal & " was found on " & .Name _
& " in " & FoundCell.Address(0, 0)
'go there and stop looking?
'Application.Goto FoundCell, Scroll:=True
'Exit For
End If
End If
End If
End With
Next wks

End Sub

By using a variable (FoundCell), you can avoid the .activate error. Then just
check to see if the value was found (with "if foundcell is nothing").



RiverGully wrote:

Starting in the first worksheet (Sheet00.Name), I wish to search all visible
worksheets for a value (value of the active cell).
(The user of the spreadsheet will click a command button for this VBA to run)
[I have turned off the On Error instruction, while testing this out...]

Private Sub FindCellContent()

'On Error GoTo addError1

Dim CellLocation As Range
Dim CellContent As String
Dim Sh As Worksheet

' Capture cell information
Set CellLocation = ActiveCell
CellContent = ActiveCell

' Start at the first Worksheet
Sheets(Sheet31.Name).Select
Range("A1").Select

' Search for CellContent in all Visible Worksheets
For Each Sh In ThisWorkbook.Worksheets
' If Sh.Visible = xlSheetVisible Then
If Cells.Find(What:=CellContent, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then
Exit For
End If
' End If
Next


'addError1:

' Application.GoTo Reference:=CellLocation
' MsgBox "Cell content not found in other worksheets"
' Exit Sub

End Sub

Thank you... Clive

--

Dave Peterson


--

Dave Peterson