Invalid next control variable reference ошибка vba

KeelPM

Пользователь

Сообщений: 8
Регистрация: 21.08.2014

Добрый день уважаемые форумчане.

Учусь самостоятельно работать в VBA, параллельно автоматизирую различные несложные рабочие сценарии.
При написании последнего макроса столкнулся с ошибкой, которую никак не получается исправить, либо найти её причины. Ошибка наверняка очень глупая, либо я просто нарушил какое-то неизвестное мне фундаментальное правило в написании.

К делу. Имеется макрос, который отыскивает на листе «Трафик» в столбце I значения «Ок» или «Внимание». В случае нахождения — копирует некоторые данные строки в которой нашел нужное значение на лист Автоматические тесты» и переходит к следующей строке, пока не дойдет до 60й, на которой его работа заканчивается. Макрос полностью рабочий, вот он.

Sub Razchet()

Dim List As String

Application.ScreenUpdating = False

List = "Трафик"

For x = 12 To 60

    Worksheets("Трафик").Activate        If Cells(x, 9).Value = "Ок" Or Cells(x, 9).Value = "Внимание" Then
        Worksheets("Автоматические тесты").Activate
            Cells((x - 1), 11).Value = Worksheets(List).Cells(2, 2)
            Cells((x - 1), 12).Value = Worksheets(List).Cells(2, 3)
            Cells((x - 1), 13).Value = Worksheets(List).Cells(5, 2)
            Cells((x - 1), 14).Select
            ActiveCell.FormulaR1C1 = "Высокий"
            Cells((x - 1), 15).Value = Worksheets(List).Cells(x, 1)
            Cells((x - 1), 16).Value = Worksheets(List).Cells(x, 8)
                If Worksheets(List).Cells(x, 9) = "îê" Then
                    Cells((x - 1), 17).Select
                        ActiveCell.FormulaR1C1 = "Завершено, ошибок нет"                                                            Else:
                End If
                If Worksheets(List).Cells(x, 9) = "Внимание" Then
                    Cells((x - 1), 17).Select
                        ActiveCell.FormulaR1C1 = "Завершено, есть ошибки"                                                            Else:
                End If
            Cells((x - 1), 18).Value = Worksheets(List).Cells(x, 11)
            Cells((x - 1), 19).Value = Worksheets(List).Cells(x, 5)
            Cells((x - 1), 20).Value = Worksheets(List).Cells(x, 10)
                                                                        Else:
    End If
    
Next x
        
End Sub 

Далее я модернизировал макрос до такого вида:

Sub Razchet()

Dim List As String

Application.ScreenUpdating = False

List = "Трафик"

For x = 12 To 60 
For y = 11 To 59

    Worksheets("Трафик").Activate        If Cells(x, 9).Value = "Ок" Or Cells(x, 9).Value = "Внимание" Then
        Worksheets("Автоматические тесты").Activate
            Cells(y, 11).Value = Worksheets(List).Cells(2, 2)
            Cells(y, 12).Value = Worksheets(List).Cells(2, 3)
            Cells(y, 13).Value = Worksheets(List).Cells(5, 2)
            Cells(y, 14).Select
            ActiveCell.FormulaR1C1 = "Высокий"
            Cells(y, 15).Value = Worksheets(List).Cells(x, 1)
            Cells(y, 16).Value = Worksheets(List).Cells(x, 8)
                If Worksheets(List).Cells(x, 9) = "îê" Then
                    Cells(y, 17).Select
                        ActiveCell.FormulaR1C1 = "Завершено, ошибок нет"                                                            Else:
                End If
                If Worksheets(List).Cells(x, 9) = "Внимание" Then
                    Cells(y, 17).Select
                        ActiveCell.FormulaR1C1 = "Завершено, есть ошибки"                                                            Else:
                End If
            Cells(y, 18).Value = Worksheets(List).Cells(x, 11)
            Cells(y, 19).Value = Worksheets(List).Cells(x, 5)
            Cells(y, 20).Value = Worksheets(List).Cells(x, 10)
                                                                        Else:
    End If
    
Next x 
Next y
        
End Sub 

и работать он перестал выдавая invalid next variable control reference.

Помогите найти ошибку (и не сильно ругайте если она окажется глупой).

Спасибо!

score:3

Sub Demo()
    Dim srcSht As Worksheet, destSht As Worksheet
    Dim lastRow As Long
    Dim cel As Range, rng As Range

    Set srcSht = ThisWorkbook.Sheets("Sheet1")  'this is your source sheet
    Set destSht = ThisWorkbook.Sheets("Sheet2") 'this is your destination sheet

    With srcSht
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row    'get last row with data in Column A of srcSht
        For Each cel In .Range("A2:A" & lastRow)            'loop through each cell in Column A of srcSht
            cel.Copy destSht.Range("R1")                    'copy cell in Column A of srcSht to Cell R1 of destSht
            cel.Offset(0, 1).Copy destSht.Range("I7")       'copy cell in Column B of srcSht to Cell I7 of destSht

            Set rng = Union(destSht.Range("R1"), destSht.Range("I7"))   'union cell R1 and I7

            With rng.Font               'format union range
                .Name = "Calibri"
                .Size = 20
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ThemeColor = xlThemeColorLight1
                .TintAndShade = 0
                .ThemeFont = xlThemeFontMinor
            End With

            destSht.Range("I7").Font.Size = 16

            'I've not tested save as pdf file part
            destSht.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=ThisWorkbook.Path & "" & (cel.Row - 1) & ".pdf", _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=True, _
                OpenAfterPublish:=False
        Next cel
    End With
End Sub

Related Query

• Compile error invalid Next Control Variable Reference VBA
• Error message: Invalid next control variable reference
• Invalid Next control variable reference ERROR
• «Invalid Next Control Variable Reference» Error in VBA Excel 2003
• VBA for loop with two input variables returning «invalid next control variable reference» error
• Invalid Next Control variable Reference
• «Invalid Next Control Variable reference» Error in excel vba
• VBA compile error if Instr function used with named parameter and return value assigned to variable
• Access VBA Compile error Invalid ParamArray Use
• Avoid Compile Error with a global variable dependant on a reference enabled during script
• excel 2010 vba loop through declared variabels syntax results in Compile error Variable not defined
• Next without For Vba Compile error
• Variable not defined error in image control variable in vba
• VBA AutoCAD Compile Error variable not defined
• VBA Compile Error: «For control variable already in use»
• Trap VBE compile error when running editor under VBA control
• VBA — Why do I get a compile error expected function or variable
• compile error variable not defined VBA
• VBA Invalid or Unqualified Reference Compile
• VBA reference to listbox form control by variable name
• An compile error in VBA after requiring variable declaration
• Added an extra variable to a VBA function — now has compile error stating Expected:=
• VBA Access Compile Error variable not defined
• Invalid Next Control Variable
• VBA Excel — Compile Error — Expected function or variable
• Excel VBA Error: Compile Error Next without For
• Need to correct VBA code. Getting error message.»For Control Variable already in use»
• Excel VBA Code: Compile Error in x64 Version (‘PtrSafe’ attribute required)
• simple vba code gives me run time error 91 object variable or with block not set
• VBA RegExp causes Compile error while vbscript.regexp works

More Query from same tag

• Deleting rows when two blank rows are concurrent
• update specific cell value when specific cell value is changed
• Use Exit Code Cleanup When Terminating Code Prematurely
• VBA Accessing data structure
• Best way to link two worksheets in excel- vba
• Filling List in VBA with a loop
• How to code Excel VBA equivalent of INDIRECT function?
• How to overwrite the data in database with new data coming from other excel files using VBA?
• VBA error 1004, can’t find path of folder variable, even though path exists and folder variable holds correct path
• Inconsistent behavior for Me keyword
• VBA Copy Whole Column From SQL Recordset
• How to define file directory in VBA
• How can I refer to AutoCAD block
• How to extract URL’s with specific text?
• Fire an Outlook 2003 macro when the user creates a new blank message
• VBA Saving (and converting) a Document From .docm to .docx
• Excel VBA process csv string into array
• How to change the same title of X-Y axis and formatin in many graphs at once using VBA?
• How can I implement search within more than one field in text box?
• Calling Worksheet_SelectionChange (Target) from inside Worksheet_Change (Target)
• VBA Macros not working first time after download
• Save Specific single sheet as .csv
• Select directory in Save As dialog box
• function for showing unique cells
• Excel 2010: how to use autocomplete in validation list
• Add new listrow is giving me a row number 2 on from the last row instead of just 1
• Update other tables when new row inserted in lead table same worksheet
• Excel VBA that searches by header name not column
• Complex If Statements VBA
• Macro VBA to delete a row only if it doesn’t contain the word «keep» in column BR