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.
Помогите найти ошибку (и не сильно ругайте если она окажется глупой).
Спасибо!
- 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
Hi,
I am getting the same ERROR massage for the below code.
Please help in it.
open_db1
open_db2
‘str_key = «SELECT distinct » & str_Query_MailBody & » FROM [MasterData_Domestic_5284$] where » & str_key & «=» & key_data(i)
keylist1 = Fetch_keylist2(str_key)
keycount1 = UBound(VBA.Split(keylist1, «,»)) + 1
key_data1 = VBA.Split(keylist1, «,»)
For i = 0 To keycount1 — 1
strSql = «SELECT » & str_Query_MailBody & » FROM [MasterData_Domestic_5284$] where » & str_key & «=» & key_data1(i)
Set rs1 = db1.OpenRecordset(strSql)
If rs1 Is Nothing Then
MsgBox «Error in All Fails data fetch!», vbExclamation, ThisWorkbook.Name
Exit Sub
End If
‘To Form the page and header
rs1.MoveFirst
rs1.MoveLast
reccount = rs1.RecordCount
rs1.MoveFirst
str = «»
If (Not rs1.EOF) Then
‘ str_MailTo = rs1(str_MailTo) & «»
str_MailCc = rs1(str_MailCc) & «»
‘str_MailBCc = rs1(str_MailBCc) & «»
‘ str_MailAddressTo = rs1(str_MailAddressTo) & «»
‘str_MailAddressTo1 = str_MailAddressTo
‘For Each fld In rs1.Fields
‘ str_MailAddressTo1 = Replace(str_MailAddressTo1, «[» & fld.Name & «]», rs1(fld.Name))
‘ Next
str_attachmentFile1 = str_attachmentFile
For Each fld In rs1.Fields
str_attachmentFile1 = Replace(str_attachmentFile1, «[» & fld.Name & «]», rs1(fld.Name))
Next
str_Subject1 = str_Subject
For Each fld In rs1.Fields
str_Subject1 = Replace(str_Subject1, «[» & fld.Name & «]», rs1(fld.Name))
Next
str_MailAddressTo1 = str_MailAddressTo
For Each fld In rs1.Fields
str_MailAddressTo1 = Replace(str_MailAddressTo1, «[» & fld.Name & «]», rs1(fld.Name))
Next
str_MailBody1_1 = str_MailBody1
For Each fld In rs1.Fields
str_MailBody1_1 = Replace(str_MailBody1_1, «[» & fld.Name & «]», rs1(fld.Name))
Next
str_MailBody2_1 = str_MailBody2
For Each fld In rs1.Fields
str_MailBody2_1 = Replace(str_MailBody2_1, «[» & fld.Name & «]», rs1(fld.Name))
Next
For Each fld In rs1.Fields
‘for customized signature (MANOJ)
SigString = «C:Documents and Settings» & Environ(«username») & _
«Application DataMicrosoftSignaturesSignature.txt»
‘for customized signature (MANOJ)
If Dir(SigString) <> «» Then
Signature = GetBoiler(SigString)
Else
Signature = «»
End If
Next
End If
‘ rs1.Close
‘ Set rs1 = Nothing
strsq2 = «SELECT » & str_Query_MailBody2 & » FROM [ContactList_Domestic_5284$] where » & str_key & «=» & key_data1(i)
Set rs2 = db2.OpenRecordset(strsq2) ‘added by MANOJ for ContactList_International
If rs2 Is Nothing Then
MsgBox «Error in Fetching MailBody Data!», vbExclamation, ThisWorkbook.Name
Exit Sub
End If
‘To Form the page and header
rs2.MoveFirst
rs2.MoveLast
reccount = rs2.RecordCount
rs2.MoveFirst
str = «»
If (Not rs2.EOF) Then
str_MailTo = rs1(str_MailTo) & «»
str_MailTo1 = str_MailTo
For Each fld In rs2.Fields
str_MailTo1 = Replace(str_MailTo, «[» & fld.Name & «]», rs2(fld.Name))
str_MailTo1 = rs2(str_MailTo) & «»
Next
Else
str_MailTo1 = «» ‘added by MANOJ for ContactList_International uniqueness
End If
str_MailAddressTo = rs1(str_MailAddressTo) & «»
str_MailAddressTo1 = str_MailAddressTo
For Each fld In rs2.Fields
str_MailAddressTo1 = Replace(str_MailAddressTo1, «[» & fld.Name & «]», rs2(fld.Name))
rs2.Close
Set rs2 = Nothing
‘rs1.Close
‘Set rs1 = Nothing
Form_mail str_MailAddressTo1 & str_MailBody1_1 & Signature, str_content, str_MailAddressTo1 & str_MailBody2_1 & Signature, str_attachmentFile1, str_MailTo1, str_MailCc, str_MailFrom, str_MailAddressTo1
rs1.MoveNext
Next i
rs1.Close
Set rs1 = Nothing
close_db2 ‘added by MANOJ for ContactList_International close
close_db1
score:3
Accepted answer
Try this
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
Note : I’ve not tested saving file as pdf part.
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
-
09-01-2022, 10:08 AM
#1
Registered User
«invalid next control variable reference» While trying to compile.
I’m trying to make a Multiple Solver using VBA in excel to make my work go faster.
I cant seem to figure out what i’m doing wrong even tho this should be super simple. (relative new to VBA and Excel)
I think the error has something to do with my «For i» and my «Next i»=?
Hope someone can hlep me out!PHP Code:
Sub MultipleSolver()
Dim i As Integer
Dim o As Integer
ActivateWorkbook.ActivateSheet.Activate
For i = 36 To 37
For o = 39 To 51
SolverReset
SolverOk SetCell:="$C$" & i, MaxMinVal:=3, ValueOf:=0, ByChange:="$H$" & o
SolverSolve True
Next i
Next oEnd Sub
Last edited by Juhl94; 09-01-2022 at 10:25 AM.
-
09-01-2022, 10:10 AM
#2
Re: «invalid next control variable reference» While trying to compile.
The Next o line needs to go before the Next i
-
09-01-2022, 10:12 AM
#3
Registered User
Re: «invalid next control variable reference» While trying to compile.
Awsome! didnt realise that…
Sadly it now says «Object required»?
-
09-01-2022, 10:23 AM
#4
Re: «invalid next control variable reference» While trying to compile.
On which line?
You can also get rid of this line as it doesn’t do anything
-
09-01-2022, 10:25 AM
#5
Registered User
Re: «invalid next control variable reference» While trying to compile.
It worked when i removed that line! i just tryed to follow a youtube vid! Thanks alot!
-
09-01-2022, 10:27 AM
#6
Re: «invalid next control variable reference» While trying to compile.
Glad to help & thanks for the feedback.
Just noticed that it should actually have been but as I said it’s not needed.
«invalid next control variable reference» While trying to compile.Hi All!
I am doing an assignment for a class i am taking to learn coding. I have been working on this one all week and keep getting various errors. The one I need right now is for a «For Without Next» . I have nested loops trying to loop in multiple worksheets and so I assume its something to do with that…but from what I see I do have open and closing loops so not sure where I am going wrong. Can you help…please see current code below.
Also my color conditional is not working so if you have a better suggestion, please advise. a
Sub StockMarket()
‘List all Variables
Dim ticker As String
Dim vol As Integer
Dim year_start As Double
Dim year_end As Double
Dim yrly_chg As Double
Dim percent_chg As Double
Dim summary_table_row As Integer
Dim ws As Worksheet
On Error Resume Next
‘run through each worksheet
For Each ws In ThisWorkbook.Worksheets
‘set headers
ws.Cells(1, 9).Value = «Ticker»
ws.Cells(1, 10).Value = «Yearly Change»
ws.Cells(1, 11).Value = «Percent Change»
ws.Cells(summary_table_row, 11).Value = percent_change
ws.Cells(1, 12).Value = «Total Stock Volume»
ws.Cells(1, 16).Value = «Ticker»
ws.Cells(1, 17).Value = «Value»
ws.Cells(2, 15).Value = «Greatest % Increase»
ws.Cells(3, 15).Value = «Greatest % Decrease»
ws.Cells(4, 15).Value = «Greatest Total Volume»
‘loop
summary_table_row = 2
‘setting the worksheet count
ws_ct = ThisWorkbook.Worksheets.Count
‘loop through worksheet
For a = 1 To ws_ct
‘loop
For i = 2 To ws.UsedRange.Rows.Count
If ws.Cells(i + 1, 1).Value <> ws.Cells(i, 1).Value Then
‘find all the values
ticker = ws.Cells(i, 1).Value
vol = ws.Cells(i, 7).Value
year_open = ws.Cells(i, 3).Value
year_close = ws.Cells(i, 6).Value
yearly_change = year_close — year_open
percent_change = (year_close — year_open) / year_close
‘add the percent sign to the value
percent_change = (Str(percent_change) & «%»)
‘insert values into summary
ws.Cells(summary_table_row, 9).Value = ticker
ws.Cells(summary_table_row, 10).Value = yearly_change
If ws.Cells(i, 10).Value > 0 Then
ws.Cells(i, 10).Interior.ColorIndex = 3 ‘ Red
Else
ws.Cells(i, 10).Interior.ColorIndex = 4 ‘ Green
End If
ws.Cells(summary_table_row, 11).Value = percent_change
If ws.Cells(i, 11).Value > 0 Then
ws.Cells(i, 11).Interior.ColorIndex = 3 ‘ Red
Else
ws.Cells(i, 11).Interior.ColorIndex = 4 ‘ Green
End If
ws.Cells(summary_table_row, 12).Value = vol
summary_table_row = summary_table_row + 1
vol = 0
End If
‘finish loop
Next i
‘move to next worksheet
Next ws
End Sub