Results 1 to 5 of 5

Thread: For-Next loop Syntax error
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular rjplante's Avatar
    Join Date
    Oct 2008
    Posts
    413
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default For-Next loop Syntax error

    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!

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,052
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: For-Next loop Syntax error

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,201
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: For-Next loop Syntax error

    The code has other problems.

    Quote Originally Posted by rjplante View Post

    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
                    If it is the first sheet, then you will not be able to use ActiveSheet.Index - 1, send error.
                    ActiveSheet.Range("AM4").Value = Sheets(ActiveSheet.Index - 1).Range("AM4").Value + 1
    
                    If it is not the first sheet, but a sheet with the same name already exists, send error.
                    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 by DanteAmor; Jul 23rd, 2019 at 12:59 PM.
    Regards Dante Amor

  4. #4
    Board Regular rjplante's Avatar
    Join Date
    Oct 2008
    Posts
    413
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: For-Next loop Syntax error

    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.

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,201
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: For-Next loop Syntax error

    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.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •