| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value of active cell in other visible worksheets
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find value of active cell in other visible worksheets
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find value of active cell in other visible worksheets
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find value of active cell in other visible worksheets
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find value of active cell in other visible worksheets
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find value of active cell in other visible worksheets
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find value of active cell in other visible worksheets
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find value of active cell in other visible worksheets
Worked perfectly! Many thanks for your support. Regards... Clive
"Jacob Skaria" wrote: 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find value of active cell in other visible worksheets
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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find previous active cell | Excel Programming | |||
| Find address of active cell | Excel Worksheet Functions | |||
| find range name for active cell | Excel Programming | |||
| find text in a cell and make cell 2 cells below that active | Excel Programming | |||
| Find active cell | Excel Programming | |||
Linear Mode
