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

[fahmoux]

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.

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

 

[Greg Truby]

Yes, because your For/Next statement is not referencing the same variable. You undoubtedly meant to write

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

for t = 83 to 82 Step -1

Example:

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

 

[fahmoux]

Hello Greg,
thank you for your help
I corrected the name of the variable and added (STEP-1) see below

     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

 

[Greg Truby]

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.

sheets("random count").range("B2").resize(60,1).value = sheets("main").range("C7").Resize(60,1).Value

 

[GTO]

Just to mention...

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:

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

 

[fahmoux]

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))...

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

 

[Greg Truby]

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))...

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.