Excel crashing while running a code

[rinijg]

I have a code to find out for a particular text in all the worksheets of a workbook and if a text is there in a particular worksheet, the name of that worksheet will be be copies to a cell in Sheet1. Following is the code

Sub searchname_Click()
     
    Dim ThisAddress$, Found, FirstAddress
    Dim Lost$, N&, NextSheet&
    Dim CurrentArea As Range, SelectedRegion As Range
    Dim Reply As VbMsgBoxResult
    Dim FirstSheet As Worksheet
    Dim Ws As Worksheet
    Dim Wks As Worksheet
    Dim Sht As Worksheet
 
    Set FirstSheet = ActiveSheet '< bookmark start sheet
    Lost = InputBox(prompt:="Type in the   details you are looking for!", _
    Title:=" Find what?", Default:="*")
    If Lost = Empty Then End
    For Each Ws In Worksheets
        Ws.Select
        With ActiveSheet.Cells
            Set FirstAddress = .Find(What:=Lost, LookIn:=xlValues)
            If FirstAddress Is Nothing Then '< blank sheet
                Next Ws
            End If
            FirstAddress.Select
            
            With Selection
                Set Found = .Find(What:=Lost, LookIn:=xlValues)
                If Not Found Is Nothing Then
                    FirstAddress = Found.Address
                  
                End If
            End With
            Selection.Copy
              
          Worksheets("Sheet1").Range("a65536").End(xlUp).Offset(1, 0).Value = ActiveSheet.Name
           
           Next Ws
 FirstSheet.Select
   
End Sub

But the excel is crashing when i run this code. Could you please help me out??

 

[Sektor]

Set FirstAddress = .Find(What:=Lost, LookIn:=xlValues)

If FirstAddress Is Nothing Then '< blank sheet
    [B][COLOR="Red"]Next Ws[/COLOR][/B]  [COLOR="SeaGreen"]' <--------- WHAT IS IT???[/COLOR]
End If

 

[rinijg]

if the text is not present in the active worksheet, go to next worksheet. Is it wrong?

 

[Sektor]

In VBE go to: Debug -> Compile - and you'll see whether it's correct.

 

[rinijg]

so what should i put there? sorry i cannot think of any other ways to write this code. Could you please help me out?

 

[Sektor]

1: Set FirstAddress = .Find(What:=Lost, LookIn:=xlValues)
.....
2: FirstAddress = Found.Address

On line 1 FirstAddress is Range. On line 2 FirstAddress is String. Then you do nothing with this FirstAddress. And your code is messed up.

 

[Sektor]

Try this.

Sub searchname_Click()
     
    Dim Lost As Variant
    Dim rngFound As Range, sh As Worksheet, shOutput As Worksheet
 
    Set shOutput = Worksheets("Sheet1")
    
    Lost = InputBox(prompt:="Type in the details you are looking for!", _
                    Title:=" Find what?", Default:="*")
    
    If Lost = "" Then Exit Sub
    
    For Each sh In Worksheets
        
        If Not sh.Name = shOutput.Name Then
        
            With sh.UsedRange
                
                Set rngFound = .Find(What:=Lost, LookIn:=xlValues)
                
                If Not rngFound Is Nothing Then
                      
                    shOutput.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = sh.Name
                
                End If
                
            End With
            
        End If
        
    Next

    shOutput.Activate
   
End Sub

 

[rinijg]

Sub searchname_Click()
     
    Dim ThisAddress$, Found, FirstAddress
    Dim Lost$, N&, NextSheet&
    Dim CurrentArea As Range, SelectedRegion As Range
    Dim Reply As VbMsgBoxResult
    Dim FirstSheet As Worksheet
    Dim Ws As Worksheet
    Dim Wks As Worksheet
    Dim Sht As Worksheet
 Dim first_address As String
    Set FirstSheet = ActiveSheet '< bookmark start sheet
    Lost = InputBox(prompt:="Type in the   details you are looking for!", _
    Title:=" Find what?", Default:="*")
    If Lost = Empty Then End
    For Each Ws In Worksheets
        Ws.Select
        With ActiveSheet.Cells
            Set FirstAddress = .Find(What:=Lost, LookIn:=xlValues)
            If FirstAddress Is Nothing Then '< blank sheet
            GoTo NextSheet
          End If
            FirstAddress.Select
            
            With Selection
                Set Found = .Find(What:=Lost, LookIn:=xlValues)
                If Not Found Is Nothing Then
                    first_address = Found.Address
                  
                End If
            End With
            Selection.Copy
              
          Worksheets("Sheet1").Range("a65536").End(xlUp).Value = ActiveSheet.Name
              End With
           Next Ws
GoTo NextSheet:
 Next Ws
  FirstSheet.Select
End Sub

I changed the code like this. Now its showing error "next with no for" for GoTo NextSheet line.
Can you please help me? I am new to VBA and hence its eating my brain

 

[Sektor]

          Worksheets("Sheet1").Range("a65536").End(xlUp).Value = ActiveSheet.Name
              End With
           Next Ws
[B][COLOR="Red"]  FirstSheet.Select

End Sub[/COLOR][/B]

GoTo NextSheet:
 Next Ws

BTW, did you try my code?

 

[rinijg]

I am sorry, I saw your code after posting my reply. I tried it now. Its working great

But if a text is present in more than 2 worksheets, in the output sheet, its showing only one worksheets name