VBA- error message: Compile Error Invalid Next Control Variable Reference

fahmoux

New Member
Joined
Aug 9, 2017
Messages
3
Hello,
I'm trying to run this code ( see below), but I get the following error message: Compile Error Invalid Next Control Variable Reference.
The variable "Fila" at the end is highlighted: "File:83 to 82" .
Thanks for your help.

Code:
Sub Run()
 
Dim Celda1 As String
Dim p10, p50, p90, NumIter As Single
Dim Fila, V, S, I1, I2, I3, J3, J4, J5 As Integer
 
Sheets("Main").Select
NumIter = Sheets("Main").Cells(3, 3).Value
 
S = 1  ' Scenario Number
I1 = 1 ' Define I1 as first I for each scenario
I2 = 1 ' Define I2 as last I for each scenario
V = 2  ' First Fila Number
 
I3 = 1
J3 = 2
J5 = 2
 
For S = 1 To 24
 
If Worksheets("main").Cells(5 + S, 16).Value = "Yes" Then
 
Worksheets("Economics").Range("Z2").Value = S
 
Sheets("Random Count").Select
For i = 1 To NumIter
 
 
 Sheets("Random Count").Cells(1, J3).Value = S & "_" & i
 Sheets("Main").Select
 Range("D7").Select
 
         For Each Cell In Range("D7:D65")  'i.e. each variable to be ramdomized
  
          Celda1 = ActiveCell
      
       Select Case Celda1
        Case "Uniform"
          Call Uniforme
        Case "Triangular"
          Call Triangular
        Case "Normal"
          Call Normal
        Case "LogNormal"
          Call LogNormal
        Case "UniformInteger"
          Call UniformInteger
        Case ""
        ActiveCell.Offset(1, 0).Select
        End Select
      
       Next Cell
      
       'If Range("L28") = "N" Then
       'Call CalculoOOIP
       'Else
       'End If
 
       ' Ramdon Numbers in "Main" in Range(B7:B67)
       ' Main Economic KPI's in "Economics" Range("D2:Y2")
       ' Oil, Water, Gas, WI, GI, Revenue Profiles in in "Economics" Range("D3:I34")
 
        Fila = 2
        For t = 1 To 60: Sheets("Random Count").Cells(Fila, (J3)).Value = Sheets("Main").Cells(t + 6, 3): Fila = Fila + 1: Next t
        For t = 1 To 22: Sheets("Random Count").Cells(Fila, (J3)).Value = Sheets("Economics").Cells(2, Fila - 57): Fila = Fila + 1: Next t

        For Fila = 83 To 82: Sheets("Random Count").Cells(Fila, (J3)).Value = Sheets("Economics").Cells(2, Fila - 57): Fila = Fila + 1: Next t

 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Yes, because your For/Next statement is not referencing the same variable. You undoubtedly meant to write
Code:
for t = 83 to 82
However, be aware that this won't do anything unless you provide a STEP, i.e. you would need to code
Code:
for t = 83 to 82 Step -1

Example:
Code:
Sub foo()
    Debug.Print "i:" & vbLf & String(15, "—")
    For i = 1 To 3: Debug.Print "i = "; i: Next i
    Debug.Print "j:" & vbLf & String(15, "—")
    Rem (the following loop does nothing)
    For j = 13 To 11: Debug.Print "j = "; j: Next j
    Debug.Print "k:" & vbLf & String(15, "—")
    For k = 23 To 21 Step -1: Debug.Print "k = "; k: Next k
End Sub
 
Last edited:
Upvote 0
Hello Greg,
thank you for your help
I corrected the name of the variable and added (STEP-1) see below

Code:
     Fila = 2
        For t = 1 To 60: Sheets("Random Count").Cells(Fila, (J3)).Value = Sheets("Main").Cells(t + 6, 3): Fila = Fila + 1: Next t
        For t = 1 To 22: Sheets("Random Count").Cells(Fila, (J3)).Value = Sheets("Economics").Cells(2, Fila - 57): Fila = Fila + 1: Next t
        For t = 83 To 82 Step -1: Sheets("Random Count").Cells(Fila, (J3)).Value = Sheets("Economics").Cells(2, Fila - 57): Fila = Fila + 1: Next t


but now I have another message error when I run the code:
Run Time Error 1004
Application defined or object defined error
 
Upvote 0
You need to step through the code using the F8 key to see where the error is occurring and then show us the line of code where things are derailing.

FWIW, your first two for t = a to b: .... : next t loops could probably be converted to simple resize() method calls. I don't know about the last one since you're decrementing.

Code:
sheets("random count").range("B2").resize(60,1).value = sheets("main").range("C7").Resize(60,1).Value
 
Last edited:
Upvote 0
Just to mention...

Code:
Dim Celda1 As StringDim p10, p50, p90, NumIter As Single
Dim Fila, V, S, I1, I2, I3, J3, J4, J5 As Integer

In the above, p10, p50 and p90 are all Variants, as are Fila, V, S, I1, I2, I3, J3 and J4. You need 'As [data type]' after each variable name. For example:

Code:
Dim Celda1 As String
Dim p10 As Single, p50 As Single, p90 As Single, NumIter As Single
Dim Fila As Long, V As Long, S As Long, I1 As Long, I2 As Long, I3 As Long, J3 As Long, J4 As Long, J5 As Long
 
Upvote 0
GTO> thank you :)
Greg> thanks you, I stepped through the code by using the F8 and while the code is running the V loop, the error message box appears : "1004 Run time error" and the cursor is just before the : "Worksheets("Random Count").Cells(V, (1 + J3))...

Code:
Next i


    Sheets("Random Count").Cells(1, (1 + J3)) = S & "_P10"
    Sheets("Random Count").Cells(1, (2 + J3)) = S & "_P50"
    Sheets("Random Count").Cells(1, (3 + J3)) = S & "_P90"
    Sheets("Random Count").Cells(1, (4 + J3)) = S & "_SD"




For V = 2 To 402


    Worksheets("Random Count").Cells(V, (1 + J3)).Value = WorksheetFunction.Percentile(Worksheets("Random Count").Range(Worksheets("Random Count").Cells(V, I1), Worksheets("Random Count").Cells(V, I2 + 1)), 0.1)
    Worksheets("Random Count").Cells(V, (2 + J3)).Value = WorksheetFunction.Percentile(Worksheets("Random Count").Range(Worksheets("Random Count").Cells(V, I1), Worksheets("Random Count").Cells(V, I2 + 1)), 0.5)
    Worksheets("Random Count").Cells(V, (3 + J3)).Value = WorksheetFunction.Percentile(Worksheets("Random Count").Range(Worksheets("Random Count").Cells(V, I1), Worksheets("Random Count").Cells(V, I2 + 1)), 0.9)
 
    If Worksheets("Random Count").Cells(V, (2 + J3)).Value <> 0 Then
        Worksheets("Random Count").Cells(V, (4 + J3)).Value = (Worksheets("Random Count").Cells(V, (4 + i)).Value - Worksheets("Random Count").Cells(V, (2 + i)).Value) / (2 * Worksheets("Random Count").Cells(V, (3 + i)).Value)
    Else
        Worksheets("Random Count").Cells(V, (4 + J3)).Value = 0
    End If


Next V
 
Upvote 0
GTO> thank you :)
Greg> thanks you, I stepped through the code by using the F8 and while the code is running the V loop, the error message box appears : "1004 Run time error" and the cursor is just before the : "Worksheets("Random Count").Cells(V, (1 + J3))...

Code:
Next i


    Sheets("Random Count").Cells(1, (1 + J3)) = S & "_P10"
    Sheets("Random Count").Cells(1, (2 + J3)) = S & "_P50"
    Sheets("Random Count").Cells(1, (3 + J3)) = S & "_P90"
    Sheets("Random Count").Cells(1, (4 + J3)) = S & "_SD"




For V = 2 To 402


[COLOR=#ff0000]    Worksheets("Random Count").Cells(V, (1 + J3)).Value = WorksheetFunction.Percentile(Worksheets("Random Count").Range(Worksheets("Random Count").Cells(V, I1), Worksheets("Random Count").Cells(V, I2 + 1)), 0.1)[/COLOR]
    Worksheets("Random Count").Cells(V, (2 + J3)).Value = WorksheetFunction.Percentile(Worksheets("Random Count").Range(Worksheets("Random Count").Cells(V, I1), Worksheets("Random Count").Cells(V, I2 + 1)), 0.5)
    Worksheets("Random Count").Cells(V, (3 + J3)).Value = WorksheetFunction.Percentile(Worksheets("Random Count").Range(Worksheets("Random Count").Cells(V, I1), Worksheets("Random Count").Cells(V, I2 + 1)), 0.9)
 
    If Worksheets("Random Count").Cells(V, (2 + J3)).Value <> 0 Then
        Worksheets("Random Count").Cells(V, (4 + J3)).Value = (Worksheets("Random Count").Cells(V, (4 + i)).Value - Worksheets("Random Count").Cells(V, (2 + i)).Value) / (2 * Worksheets("Random Count").Cells(V, (3 + i)).Value)
    Else
        Worksheets("Random Count").Cells(V, (4 + J3)).Value = 0
    End If


Next V

You may want to use the text color tools to highlight the row in question. I'm assuming is the one I indicate.

But, even if it's a different row, what you need to do is Add a Watch on the variable V to see what's going on. You can highlight V and right-click and select Add Watch... or you can use the menu in the VBE. Set watches on V and any other key variables to make sure their values are what you think they should be and to know precisely when the error is happening. You can expressions in the Watch window, not just variables. In other words you could set a watch on Worksheets("Random Count").Cells(V, (1 + J3)).Address(0,0) and see if that is pointing to the cell you think it ought to be pointing to.

Then step the code again using the F8 key and see what's going on with your variables as you execute each line of the macro.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top