For-Next loop Syntax error

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
558
Office Version
  1. 365
Platform
  1. Windows
I am trying to write a for next loop and I am getting a "Compile Error: Syntax error" when I enter the phrase "Continue For".

I want the code to check the AX2 cell on each sheet, if the cell contains a "T" then I want it to execute to rest of the code. If it does NOT contain a "T" in AX2, then I want it to skip to the next sheet and check that one.

My code is below. What am I doing wrong here?

Code:
    For Each xlSheet In ThisWorkbook.Sheets
    
        If ActiveSheet.Range("AX2").Value <> "T" Then
            Continue For
        Else
            If ActiveSheet.Name = "T-01 VAL-4840 Control Box" Then
                ActiveSheet.Range("AM4").Value = 1
            Else
                ActiveSheet.Range("AM4").Value = Sheets(ActiveSheet.Index - 1).Range("AM4").Value + 1
                ActiveSheet.Name = [CA1]
                If ActiveSheet.Index < Worksheets.Count Then
                    Sheets(ActiveSheet.Index + 1).Activate
                Else
                    Exit For
                End If
            End If
        End If

    Next xlSheet

Thanks for any help provided!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
How about
Code:
For Each xlsheet In ThisWorkbook.Sheets
    
        If xlsheet.Range("AX2").Value = "T" Then
            If xlsheet.Name = "T-01 VAL-4840 Control Box" Then
                xlsheet.Range("AM4").Value = 1
            Else
                xlsheet.Range("AM4").Value = Sheets(xlsheet.Index - 1).Range("AM4").Value + 1
                xlsheet.Name = [CA1]
                If xlsheet.Index < Worksheets.Count Then
                    Sheets(xlsheet.Index + 1).Activate
                Else
                    Exit For
                End If
            End If
        End If

    Next xlsheet
 
Upvote 0
The code has other problems.

Code:
    For Each xlSheet In ThisWorkbook.Sheets
    
        If ActiveSheet.Range("AX2").Value <> "T" Then
            Continue For
        Else
            If ActiveSheet.Name = "T-01 VAL-4840 Control Box" Then
                ActiveSheet.Range("AM4").Value = 1
            Else
[COLOR=#ff0000]                If it is the first sheet, then you will not be able to use ActiveSheet.Index - 1, send error.[/COLOR]
                ActiveSheet.Range("AM4").Value = Sheets(ActiveSheet.Index - 1).Range("AM4").Value + 1

                [COLOR=#ff0000]If it is not the first sheet, but a sheet with the same name already exists, send error.[/COLOR]
                ActiveSheet.Name = [CA1]
                If ActiveSheet.Index < Worksheets.Count Then
                    Sheets(ActiveSheet.Index + 1).Activate
                Else
                    Exit For
                End If
            End If
        End If

    Next xlSheet

Thanks for any help provided!

If you explain what you need to do, maybe we can help you with all the code.
 
Last edited:
Upvote 0
Here is what I need my code to do.

1) I have a sheet (Intro) with a column (B2:B30) that has the number of copies needed for each sheet in the workbook (search text is listed in Column O).
2) All the worksheets in the workbook are formatted with the same general layout with respect to key information cells (AM2, AR2, AM4, AX2, CA1, etc).
3) Cell CA1 uses the information in these key cells to generate a tab name.
4) I want my macro to start at the top of range (cell B2 of B2:B30), go to the sheet that contains the text in Col O, and make copies of that sheet that matches the number entered into in Col B.
5) I then need to go to the first worksheet with the letter "T" in cell AX2 and change the value in AM4 to 1, then loop through the remaining worksheets updating the value in AM4 by 1 for each subsequent worksheet in the list. This will change the name listed in cell CA1 to start with "T-01" through "T-XX" thereby giving each tab a unique name.
6) For the worksheets that had duplicates, I need to update the first worksheet that is not blank in cell AM2 to the number 1, and increase the subsequent worksheets that are not blank in cell AM2 by 1 for each worksheet. This will then change the name to read "T-15 Motor 01", "T-16 Motor 02", "T-17 Motor 03", etc. After updating cell AM2, I need to update the tab name to reflect the change in cell CA1.
7) Then on the Intro sheet, I need to move from cell B2 down one row to cell B3 and repeat steps 1-6 again.

Here is my entire macro:

Code:
Sub FIND_SHEET()


    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Dim CopyCnt As Integer
    Dim wsCnt As Integer
    Dim wsN As Worksheet


'   Capture number of worksheet copies to make


    CopyCnt = ActiveCell.Value - 1
    
    'MsgBox "Current sheet objective = " & CopyCnt
    
'   Find worksheet that matches text


    For Each Sheet In ActiveWorkbook.Sheets
    
        If Sheet.Name Like "*" & ActiveCell.Offset(0, 13).Value & "*" Then
            Sheet.Activate
            Set wsN = ActiveSheet
            Exit For
        End If
    Next
    
'   Duplicate selected worksheet
    
    n = 0
    
    Do Until n = CopyCnt
        wsN.Copy After:=wsN
        n = n + 1
    Loop


'   Update test number count T-00
    
For Each xlsheet In ThisWorkbook.Sheets
    
        If xlsheet.Range("AX2").Value = "T" Then
            If xlsheet.Name = "T-01 VAL-4840 Control Box" Then
                xlsheet.Range("AM4").Value = 1
            Else
                xlsheet.Range("AM4").Value = Sheets(xlsheet.Index - 1).Range("AM4").Value + 1
                xlsheet.Name = [CA1]
                If xlsheet.Index < Worksheets.Count Then
                    Sheets(xlsheet.Index + 1).Activate
                Else
                    Exit For
                End If
            End If
        End If


    Next xlsheet
    
'   Update duplicate test numbers


    Sheets("T-01 VAL-4840 Control Box ").Activate
        
    Do
        If ActiveSheet.Index < Worksheets.Count Then
            Sheets(ActiveSheet.Index + 1).Activate
        End If
            If ActiveSheet.Range("AM2").Value <> "" Then
            
                Do Until ActiveSheet.Range("AM2").Value = ""
                    If ActiveSheet.Index < Worksheets.Count Then
                        Sheets(ActiveSheet.Index + 1).Activate
                    End If
                    ActiveSheet.Range("AM2").Value = Sheets(ActiveSheet.Index - 1).Range("AM2").Value + 1
                    ActiveSheet.Name = [CA1]
                Loop
            End If
    Loop
   
    Application.EnableEvents = True
    Application.ScreenUpdating = True
   
End Sub

Thanks for the guidance. I am just a padawan learner and appreciate being in the company of coding Jedi Masters.
 
Upvote 0
I did not understand very well the duplicate sheets. But I suppose that if the sheet already exists then you have to increase the second counter.


The logic of the following macro is different from your approach.
First calculate the name of the sheet, if it does not exist create the sheet; If the sheet already exists, then increase the counter.


Try a new book and template sheets.

Run macro First_Shot.

Code:
Sub First_Shot()
    Dim sh1 As Worksheet, nw As Worksheet, c As Range, h As String
    Dim j As Long, n As Long, i As Long, nIndex As Long
    
    Application.ScreenUpdating = False
    Set sh1 = Sheets("Intro")
    For Each c In sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
        n = c.Value
        h = sh1.Cells(c.Row, "O").Value
        j = 1
        If validaSheet(h) Then
            nIndex = Sheets(h).Index
            For i = 1 To n
                Sheets(h).Copy after:=Sheets(nIndex)
                Set nw = ActiveSheet
                nw.Range("AM4").Value = j
                Do While validaSheet(nw.Range("CA1"))
                    nw.Range("AM2").Value = nw.Range("AM2").Value + 1
                Loop
                nw.Name = nw.Range("CA1")
                nIndex = nIndex + 1
                j = j + 1
            Next
        End If
    Next
End Sub


Function validaSheet(h)
    validaSheet = False
    For Each sh In Sheets
        If LCase(sh.Name) = LCase(h) Then
            validaSheet = True
            Exit For
        End If
    Next
End Function

If there is a problem, tell me as much detail as you can:
- Error message
- Error line
- Sheet name to create
- Sheet name created
- etc.
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top