For-Next loop Syntax error

rjplante

Active Member
Joined
Oct 31, 2008
Messages
475
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!
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,827
Office Version
365
Platform
Windows
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,207
Office Version
2007
Platform
Windows
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:

rjplante

Active Member
Joined
Oct 31, 2008
Messages
475
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,207
Office Version
2007
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,625
Messages
5,487,943
Members
407,616
Latest member
MichaelaL

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top