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.

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

Спасибо!

• Remove From My Forums

• Question

• Hi,

  Software runs ok until a crash takes place in line

  ‘ End of loop

  Next i

  . What is this?

  —————————
  Microsoft Visual Basic
  —————————
  Compile error:

  Invalid Next control variable reference
  —————————
  OK   Help  
  —————————

  If I press the help button

  ——————————————————————————

  No help available

  Not all errors have an associated help topic. This error has the following causes and solutions:

  You generated an error for which no Help exists.

  Check the Readme file. Help for late-breaking errors is often available through the Readme file.

  ——————————————————————————

  BR,

  ob

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

• Custom Pathing on Excel Cells
• High and low bytes in VBA
• A function or VBA code to determine the last digit in a number
• How to access a named List Style in MS Word from VBA?
• Search on a form that is filtered with a combobox
• Excel VBA — Macro that runs for entire range of cell
• VBA Break Command bound to a key
• How to get last word in a cell using Excel Formula
• Excel — Deleting images when deleting a row
• Runtime error 438 when copying from one workbook to the other
• Do While ActiveCell <> Range
• Get i’th element of an array read from an Excel range
• Passing a control name as string to the Form Object in VBA
• Sheet name as variable to get last row from function
• What would be the best way to write repetitive code like this?
• Changing bin size of Histogram
• Word VBA — How to insert references of heading number; heading text; list item number and list item page number to comment?
• Using counter within an «—[]» expression in vba
• Setting a global Scripting.Dictionary in Visual Basic for Applications (VBA)
• Submit or bypass form for a Web Query
• How to insert a variable in a countif formula in VBA?
• Hardware Enable/Disable in vb6
• Conditional sum with merged cells using Sumiif()
• Magento CSV import , CSV file with full web address , Download the images
• Microsoft Access: How to do IF in Calculated Field Expression?
• Copy userform to another workbook
• if then else statement makes exactly the same in vba excel
• Populate an Excel table with a collection object
• Handle Alert PopUp Dialog of Macro enabled Excel File using C#
• Copy range of cells to a new location