Find value of active cell in other visible worksheets

R

RiverGully

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
 
D

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:

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").


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
Click to expand...
 
R

RiverGully

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 said:
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").


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
Click to expand...
Click to expand...
 
J

Jacob Skaria

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 said:
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 said:
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").


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
Click to expand...
Click to expand...
Click to expand...
 
R

RiverGully

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

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:


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 said:
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").


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
Click to expand...
Click to expand...
Click to expand...
 
J

Jacob Skaria

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 said:
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 said:
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 said:
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


:

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
Click to expand...
Click to expand...
Click to expand...
 
R

RiverGully

Worked perfectly! Many thanks for your support. Regards... Clive

Jacob Skaria said:
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 said:
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 said:
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


:

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
R

RiverGully

Your code works, but I can't get it to work when it finds a protected
worksheet.
Can the code be such that any protected worksheets are unprotected.

Thank you.

PS If the contents being searched are not on the protected worksheet, can
it be protected again before the search continues? not as important as
unprotecting sheets that are protected.


Jacob Skaria said:
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 said:
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 said:
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


:

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


:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding content of cell in visible "protected" sheets 4
Set Active Cell to A1 on Open Q 3
Select specific worksheets & copy - code problem 2
Print Command - Array of Worksheets 5
Ron de Bruin - Merge cells from all worksheets 0
conso macro 0
VBA Excel - Select a value from one cell in Excel to update a SQL table 1
Deleting veryHidden worksheets 5

Top