+ Reply to Thread
Results 1 to 10 of 10

compile error: For control variable already in use

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-20-2009
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    467

    compile error: For control variable already in use

    I have the code below behind a command button on a worksheet and I have tried adding the bit highlighted in red to the beginning but when I try running the code now I get a 'compile error' "For control variable already in use" and when I debug it highlights the text "For i = 4 To lrow" (highlighted in red). Can anyone help with what I need to change to make this work? Please let me know if it needs further clarification of what it is supposed to be doing.

    I also want to add some code to the macro where I have highlighted in green further down. At this point in the macro I want some code that deletes rows where the value in column P is the same as column BA. Any help here would be greatly appreciated too.

    Option Explicit
    
    Sub coupon_loop()
     
    Dim lrow As Long, i As Long
    Dim fpath As String 'directory to save csv files to
    Dim rDel    As Range
    Dim cell    As Range
    
    
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = True
    Application.StatusBar = "Creating CSV Files"
    
    'Store current prices in temporary sheets so prices that have not changed on the update can be deleted later
    
    With Worksheets("Coupon")
    
        lrow = .Range("D" & .Rows.Count).End(xlUp).Row
        For i = 4 To lrow
        
    If .Cells(i, 8) >= Cells(i, 54) And .Cells(i, 9) >= .Cells(i, 55) Then GoTo GetNext
    
            Worksheets("Selections").Range("Z2").Value = .Range("J" & i).Value ' home goals
            Worksheets("Selections").Range("Z4").Value = .Range("K" & i).Value ' away goals
            Worksheets("Selections").Range("AB158").Value = .Range("L" & i).Value '1st half goals %
            Worksheets("Selections").Range("AB237").Value = .Range("M" & i).Value '2nd half goals %
            Worksheets("Selections").Range("AA3").Value = .Range("R" & i).Value '1x2 Factor
            Worksheets("Selections").Range("AG2").Value = .Range("S" & i).Value 'Draw Factor
    
    Worksheets("Selections").Range("A2:U360").Copy
    Worksheets("SelectionsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    
    
        With Sheet17
            .Range("P2", .Range("P2").End(xlDown).Offset(, 1)).Copy .Range("BA2")
        End With
    
    '** Copy current match from Events, Markets & Selections to next available row in temporary sheets
    
    With Worksheets("Coupon")
    
        lrow = .Range("D" & .Rows.Count).End(xlUp).Row
        For i = 4 To lrow
        
    If .Cells(i, 8) >= Cells(i, 54) And .Cells(i, 9) >= .Cells(i, 55) Then GoTo GetNext
    
            Worksheets("Selections").Range("B2").Value = .Range("D" & i).Value
            Worksheets("Selections").Range("C2").Value = .Range("E" & i).Value
            Worksheets("Selections").Range("E2").Value = .Range("AV" & i).Value
            Worksheets("Selections").Range("Z2").Value = .Range("J" & i).Value
            Worksheets("Selections").Range("Z4").Value = .Range("K" & i).Value
            Worksheets("Selections").Range("G2").Value = .Range("F" & i).Value
            Worksheets("Selections").Range("G4").Value = .Range("G" & i).Value
            Worksheets("Selections").Range("AB158").Value = .Range("L" & i).Value
            Worksheets("Selections").Range("AB237").Value = .Range("M" & i).Value
            Worksheets("Selections").Range("AA3").Value = .Range("R" & i).Value
            Worksheets("Selections").Range("AG2").Value = .Range("S" & i).Value
            Worksheets("Markets").Range("AB2").Value = .Range("V" & i).Value
            Worksheets("Markets").Range("AD2").Value = .Range("W" & i).Value
        
    
    Worksheets("Events").Range("A2:W2").Copy
    Worksheets("EventsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    
    Worksheets("Markets").Range("A2:AB85").Copy
    Worksheets("MarketsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    
    Sheets("MarketsTemporary").Select
    
        Set rDel = Nothing
        
        With ActiveSheet.UsedRange
            .Value = .Value
            For Each cell In Intersect(.Cells, .Columns("A"))
                If Len(cell.Text) = 0 Then
                    If rDel Is Nothing Then Set rDel = cell
                    Set rDel = Union(rDel, cell)
                End If
            Next cell
        End With
    
        If Not rDel Is Nothing Then rDel.EntireRow.Delete
    
    Worksheets("Selections").Range("A2:U360").Copy
    Worksheets("SelectionsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    
    Sheets("SelectionsTemporary").Select
    
        Set rDel = Nothing
        
        With ActiveSheet.UsedRange
            .Value = .Value
            For Each cell In Intersect(.Cells, .Columns("A"))
                If Len(cell.Text) = 0 Then
                    If rDel Is Nothing Then Set rDel = cell
                    Set rDel = Union(rDel, cell)
                End If
            Next cell
        End With
    
        If Not rDel Is Nothing Then rDel.EntireRow.Delete
        
    
    GetNext:    Next i
    End With
    
     *****Delete rows where price has not changed  *******
    'Need to add some code here that deletes any row where the value in column P is the same as the value in column BA 
    
    
    ' Remove underscores in event classes and types on temporary sheets
    
        Sheets("EventsTemporary").Select
        Columns("B:C").Select
        Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
            
        Sheets("MarketsTemporary").Select
        Columns("B:C").Select
        Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
               
        Sheets("SelectionsTemporary").Select
        Columns("B:C").Select
        Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    
    
    fpath = "C:\Documents and Settings\HOME USER\My Documents\Dropbox\_Work to be done\Footy Model csv"
    
    'fpath="C:\Documents and Settings\HOME USER\My Documents\Dropbox\_Work to be done\Footy Model csv"
    'fpath="C:\Users\Adam\Dropbox\_Work to be done"
    
    
    '**  Save temporary sheets as csv files in directory named fpath
    ThisWorkbook.Worksheets("EventsTemporary").Copy
    ActiveWorkbook.SaveAs Filename:=fpath & "\Events - " & Format(Now, "yyyy-mm-dd hh-mm-ss") & " - " & Environ$("username") & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=True
    
    ThisWorkbook.Worksheets("MarketsTemporary").Copy
    ActiveWorkbook.SaveAs Filename:=fpath & "\Markets - " & Format(Now, "yyyy-mm-dd hh-mm-ss") & " - " & Environ$("username") & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=True
    
    ThisWorkbook.Worksheets("SelectionsTemporary").Copy
    ActiveWorkbook.SaveAs Filename:=fpath & "\Selections - " & Format(Now, "yyyy-mm-dd hh-mm-ss") & " - " & Environ$("username") & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=True
    
    
    '** Clear contents of temporary sheets
    lrow = ThisWorkbook.Worksheets("EventsTemporary").Range("A" & Rows.Count).End(xlUp).Row
    ThisWorkbook.Worksheets("EventsTemporary").Range("A2:W" & lrow).ClearContents
    
    lrow = ThisWorkbook.Worksheets("MarketsTemporary").Range("A" & Rows.Count).End(xlUp).Row
    ThisWorkbook.Worksheets("MarketsTemporary").Range("A2:AD" & lrow).ClearContents
    
    lrow = ThisWorkbook.Worksheets("SelectionsTemporary").Range("A" & Rows.Count).End(xlUp).Row
    ThisWorkbook.Worksheets("SelectionsTemporary").Range("A2:Y" & lrow).ClearContents
    
    '** store latest TG & Supremacies
        With Sheet2
            .Range("H4", .Range("H4").End(xlDown).Offset(, 1)).Copy .Range("BB4")
        End With
    
    ThisWorkbook.Worksheets("Coupon").Activate
    ThisWorkbook.Worksheets("Coupon").Range("A1").Select
    
    Application.ScreenUpdating = True
    Application.StatusBar = False
    
    End Sub

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: compile error: For control variable already in use

    You can not use i inside a For next loop that your already using i. You need another variable Ex: ii

    For i = 1 To 10
        For ii = 1 To 10
        Next ii
    Next i
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Forum Contributor
    Join Date
    02-20-2009
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    467

    Re: compile error: For control variable already in use

    Thanks for your reply Mike but I'm not sure I follow how to change my code? Could you possibly post how exactly I need to change my code?

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: compile error: For control variable already in use

    Where you have Highlighted your Code in Red the Second For i = needs to be a different variable. Change it to (ii or n or r) but it cannot be (i) because its inside your First For i = loop.

  5. #5
    Forum Contributor
    Join Date
    02-20-2009
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    467

    Re: compile error: For control variable already in use

    Thanks for your reply Mike. I am partly understanding you but I have made the change you mentioned but now getting a new compiler error: "Invalid Next control variable reference" and it highlights the line "GetNext" highlighted below. So, here is now the code I have got (up to 'Delete rows where price has not changed') with what I have changed/added highlighted in red.

    Option Explicit
    
    Sub coupon_loop()
     
    Dim lrow As Long, i As Long
    Dim ii As Long
    Dim fpath As String 'directory to save csv files to
    Dim rDel    As Range
    Dim cell    As Range
    Dim ans As String 'email message box
    
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = True
    Application.StatusBar = "Creating CSV Files"
    
    'Store current prices in temporary sheets so prices that have not changed on the update can be deleted later
    
    With Worksheets("Coupon")
    
        lrow = .Range("D" & .Rows.Count).End(xlUp).Row
        For i = 4 To lrow
    
    If .Cells(i, 8) >= Cells(i, 54) And .Cells(i, 9) >= .Cells(i, 55) Then GoTo GetNext
    
            Worksheets("Selections").Range("Z2").Value = .Range("J" & i).Value ' home goals
            Worksheets("Selections").Range("Z4").Value = .Range("K" & i).Value ' away goals
            Worksheets("Selections").Range("AB158").Value = .Range("L" & i).Value '1st half goals %
            Worksheets("Selections").Range("AB237").Value = .Range("M" & i).Value '2nd half goals %
            Worksheets("Selections").Range("AA3").Value = .Range("R" & i).Value '1x2 Factor
            Worksheets("Selections").Range("AG2").Value = .Range("S" & i).Value 'Draw Factor
    
    Worksheets("Selections").Range("A2:U360").Copy
    Worksheets("SelectionsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    
    
        With Sheet17
            .Range("P2", .Range("P2").End(xlDown).Offset(, 1)).Copy .Range("BA2")
        End With
    
    '** Copy current match from Events, Markets & Selections to next available row in temporary sheets
    
    With Worksheets("Coupon")
    
        lrow = .Range("D" & .Rows.Count).End(xlUp).Row
        For ii = 4 To lrow
        
    If .Cells(i, 8) >= Cells(i, 54) And .Cells(i, 9) >= .Cells(i, 55) Then GoTo GetNext
    
            Worksheets("Selections").Range("B2").Value = .Range("D" & ii).Value
            Worksheets("Selections").Range("C2").Value = .Range("E" & ii).Value
            Worksheets("Selections").Range("E2").Value = .Range("AV" & ii).Value
            Worksheets("Selections").Range("Z2").Value = .Range("J" & ii).Value
            Worksheets("Selections").Range("Z4").Value = .Range("K" & ii).Value
            Worksheets("Selections").Range("G2").Value = .Range("F" & ii).Value
            Worksheets("Selections").Range("G4").Value = .Range("G" & ii).Value
            Worksheets("Selections").Range("AB158").Value = .Range("L" & ii).Value
            Worksheets("Selections").Range("AB237").Value = .Range("M" & ii).Value
            Worksheets("Selections").Range("AA3").Value = .Range("R" & ii).Value
            Worksheets("Selections").Range("AG2").Value = .Range("S" & ii).Value
            Worksheets("Markets").Range("AB2").Value = .Range("V" & ii).Value
            Worksheets("Markets").Range("AD2").Value = .Range("W" & ii).Value
        
    
    Worksheets("Events").Range("A2:W2").Copy
    Worksheets("EventsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    
    Worksheets("Markets").Range("A2:AB85").Copy
    Worksheets("MarketsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    
    Sheets("MarketsTemporary").Select
    
        Set rDel = Nothing
        
        With ActiveSheet.UsedRange
            .Value = .Value
            For Each cell In Intersect(.Cells, .Columns("A"))
                If Len(cell.Text) = 0 Then
                    If rDel Is Nothing Then Set rDel = cell
                    Set rDel = Union(rDel, cell)
                End If
            Next cell
        End With
    
        If Not rDel Is Nothing Then rDel.EntireRow.Delete
    
    Worksheets("Selections").Range("A2:U360").Copy
    Worksheets("SelectionsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    
    Sheets("SelectionsTemporary").Select
    
        Set rDel = Nothing
        
        With ActiveSheet.UsedRange
            .Value = .Value
            For Each cell In Intersect(.Cells, .Columns("A"))
                If Len(cell.Text) = 0 Then
                    If rDel Is Nothing Then Set rDel = cell
                    Set rDel = Union(rDel, cell)
                End If
            Next cell
        End With
    
        If Not rDel Is Nothing Then rDel.EntireRow.Delete
        
    
    GetNext:    Next i
    End With
    
    'Delete rows where price has not changed

  6. #6
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: compile error: For control variable already in use

    Only thing I can see off the bat is you dont have a Next ii.I dont know exactly want your doing but try adding a next ii like this below

        For ii = 4 To lrow
        
            If .Cells(i, 8) >= Cells(i, 54) And .Cells(i, 9) >= .Cells(i, 55) Then GoTo GetNext
    
            Worksheets("Selections").Range("B2").Value = .Range("D" & ii).Value
            Worksheets("Selections").Range("C2").Value = .Range("E" & ii).Value
            Worksheets("Selections").Range("E2").Value = .Range("AV" & ii).Value
            Worksheets("Selections").Range("Z2").Value = .Range("J" & ii).Value
            Worksheets("Selections").Range("Z4").Value = .Range("K" & ii).Value
            Worksheets("Selections").Range("G2").Value = .Range("F" & ii).Value
            Worksheets("Selections").Range("G4").Value = .Range("G" & ii).Value
            Worksheets("Selections").Range("AB158").Value = .Range("L" & ii).Value
            Worksheets("Selections").Range("AB237").Value = .Range("M" & ii).Value
            Worksheets("Selections").Range("AA3").Value = .Range("R" & ii).Value
            Worksheets("Selections").Range("AG2").Value = .Range("S" & ii).Value
            Worksheets("Markets").Range("AB2").Value = .Range("V" & ii).Value
            Worksheets("Markets").Range("AD2").Value = .Range("W" & ii).Value
        Next ii

  7. #7
    Forum Contributor
    Join Date
    02-20-2009
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    467

    Re: compile error: For control variable already in use

    ok, done that and now I am getting another compile error: "Next without For" and it highlights the word "Next" in the line

    GetNext:    Next i

  8. #8
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: compile error: For control variable already in use

    Again at just looking at your code and not knowing what exactly your doing, you need to make sure anywhere you use With Worksheet you have a end with.
    Try changing adding a End with above the Getnext

    End With
    GetNext: Next i

  9. #9
    Forum Contributor
    Join Date
    02-20-2009
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    467

    Re: compile error: For control variable already in use

    Not getting any errors when running the code now but doesn't do what it is supposed to now

    This is becoming difficult to explain and for you to fully understand without posting an example and a more detailed explanation. Give me a bit of time to prepare a version of my workbook that hides some of my more important work which I don't want to post on to the internet for confidentiality reasons.

    It may get tomorrow as I need to go somewhere right now. Thanks for your efforts so far though, hopefully we can get to the bottom of it soon!

  10. #10
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: compile error: For control variable already in use

    Ok sounds good. Post the workbook and give details of your expected output.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1