I’m sorry, i shouldve explained what i was trying to produce than asking to fix something i wanted to do. My experience in vba has been self-taught, and i’m a little new to asking for help.
The script Floris produced seemed to have function but not as intended. Turns out what i wrote is a little outdated, and needs to be wiped and restarted. This was actually an old script i started a few months back that worked off of a web-query. But the website went thru some changes and now the script is all over the place.
the main issue i was having was a compile-error «Invalid Next Control Variable Reference» Which turns out to be caused by an open ‘Do while’ loop, that doesnt seem to have much of an exit point from the research i looked up. Was supposed to have used another ‘If’ command instead. At the same time, when attempting to solve that ‘Do While’ i added an extra ‘Next’ (cause i thought they were compatible), and it screwed with the script.
Hard to explain.. But the ‘Do While’ i used, i wanted it to combine the values only if the number of values were greater
rnumbers = Rows(ActiveCell.Range("A3").End(xlDown)) + 3
'or CellCount = ActiveCell.Range("A" & Rows.Count).End(xldown).Row
Do While Rows(ActiveCell.Range("A3").End(xlDown)) > 3
But instead it was supposed to be
Dim CellCount As Range
CellCount = ActiveCell.Range("A" & Rows.Count).End(xlDown).Row + 2
'cause its the active cell + two additional cells
If CellCount > 3
Which then opens up into the script Floris submitted. (But that failed too, because of what was stated above).
Thanks again, hope that it explains everything… Sorry if i wasted your time with that one Floris, really do appreciate the assistance. Just wish i had asked for the help sooner, would have saved me a lot of frustration that i’m dealing with now. >_>
Permalink
Cannot retrieve contributors at this time
| title | keywords | f1_keywords | ms.prod | ms.assetid | ms.date | ms.localizationpriority |
|---|---|---|---|---|---|---|
|
Invalid Next control variable reference |
vblr6.chm1011226 |
vblr6.chm1011226 |
office |
1fd6eeda-b1e9-5c36-8100-b0e8ea3614fc |
06/08/2017 |
medium |
The numeric variable in the Next part of a For…Next loop must match the variable in the For part. This error has the following cause and solution:
-
The variable in the Next part of a For…Next loop differs from the variable in the For part. For example:
For Counter = 1 To 10 MyVar = Counter Next Count
Check the spelling of the variable in the Next part to be sure it matches the For part. Also, be sure you haven’t inadvertently deleted parts of the enclosing loop that used the variable.
For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).
[!includeSupport and feedback]
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.
Помогите найти ошибку (и не сильно ругайте если она окажется глупой).
Спасибо!
Запускаю этот код, выделяется переменная «y» и появляется сообщение: Invalid Next control variable reference.
В чём может быть проблема?
Sub Find_Matches()
Dim CompareRange1 As Variant, x As Variant, y As Variant, CompareRange3 As Variant
Set CompareRange1 = Range(«A1:A7»)
Set CompareRange3 = Range(«C1:C4»)
For Each y In CompareRange1
For Each x In CompareRange3
If x = y Then y.Offset(0, 1) = x.inset(0, 1)
Next y
Next x
End Sub
- 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
Answers
-
Hi
Post your code and this should be easy to spot. My guess is you have some nested loops and you have refered to the wrong variable with the next statement.
Regards
ADG