Next Without For Combile Error - For a loop

[JoeRooney]

Hi All,

I am receiving a compile error “Next without For” for a loop I am trying tocreate.

I need the codeto loop through all the spreadsheets in a workbook, search for the word investigate in column W,if found copy cell A2 and paste the value into Column B in the “Master” spreadsheetin descending order.

If “investigate”is not found ignore and move onto the next sheet.

Below is the codeI have so far.

Any help isgreatly appreciated

Sub CombineData3()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.name <> "Master" Then
Sht.Select
Dim foundsomething As Range
    Dim searchterm As String
    searchterm = "Investigate"
On Error Resume Next
    ActiveSheet.ShowAllData
    Columns("A:W").Select
    Selection.EntireColumn.Hidden = False
    Set foundsomething = Application.ActiveSheet.Find(What:="Investigate", After:=ActiveCell, lookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
        
    If (Not foundsomething Is Nothing) And columnW = "Investigate" Then
        
        Range("A2").Copy
    Worksheets("Master").Activate
    Range("B1" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        Else
Sht.Select
Next Sht
End If
End Sub

 

[JazzSP8]

It's a confusing message, it looks like you're missing an End If somewhere (or an Else).

That being said, your "Next" is also part of an IF statement it doesn't start in...?

Rough guessish, needs to be something more like...

Sub CombineData3()
Dim Sht As Worksheet
Dim foundsomething As Range
Dim searchterm As String

For Each Sht In ActiveWorkbook.Worksheets
    If Sht.Name <> "Master" Then
        Sht.Select
        searchterm = "Investigate"
        On Error Resume Next
        ActiveSheet.ShowAllData
        Columns("A:W").Select
        Selection.EntireColumn.Hidden = False
        Set foundsomething = Application.ActiveSheet.Find(What:="Investigate", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    [COLOR=#ff0000]End If[/COLOR]

    If (Not foundsomething Is Nothing) And columnW = "Investigate" Then
        Range("A2").Copy
        Worksheets("Master").Activate
        Range("B1" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Else
        Sht.Select
    End If

[COLOR=#ff0000]Next Sht[/COLOR]

End Sub

 

[Fluff]

Another possibility

   Sub CombineData3()
   Dim Sht As Worksheet
   For Each Sht In ActiveWorkbook.Worksheets
      If Sht.name <> "Master" Then
         Sht.Select
         Dim foundsomething As Range
         Dim searchterm As String
         searchterm = "Investigate"
         On Error Resume Next
         ActiveSheet.ShowAllData
         Columns("A:W").Select
         Selection.EntireColumn.Hidden = False
         Set foundsomething = Application.ActiveSheet.Find(What:="Investigate", After:=ActiveCell, lookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
         
         If (Not foundsomething Is Nothing) And columnW = "Investigate" Then
            Range("A2").Copy
            Worksheets("Master").Activate
            Range("B1" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
               :=False, Transpose:=False
         End If
      End If
      
   Next Sht
End Sub

 

[Norie]

Perhaps.

Sub CombineData3()
Dim Sht As Worksheet
Dim searchterm As String
Dim Res As Variant

    searchterm = "Investigate"
    
    For Each Sht In ActiveWorkbook.Worksheets
    
        If Sht.Name <> "Master" Then
        
            On Error Resume Next
            
            With Sht

                .ShowAllData

                .Columns("A:W").EntireColumn.Hidden = False

                Res = Application.Match("searchterm", .Range("W:W"),0)
        
                 If Not IsError(Res) Then
                     .Range("A2").Copy
                     Worksheets("Master").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
                End If

            End With
            
        End If
        
    Next Sht
    
End Sub

 

[JoeRooney]

Thanks guys for your help

 

[Fluff]

You're welcome