I wrote the following code to import text data and fill in an excel database with the data from the imported text file. The text file is then deleted, a new text file imported and the cycle repeats. However I'm getting a Compile error: Invalid Next control variable reference at Next FileNameCell How can I fix this? Thanks!


Sub importtxt()
'
' importtxt Macro
'
    Dim rngFileNames As Range:  Set rngFileNames = Sheets("Sheet1").Range("I1", Cells(Rows.Count, "I").End(xlUp))
    Dim FileNameCell As Range
    For Each FileNameCell In rngFileNames
        If FileNameCell.Value <> vbNullString Then Sheets("Sheet2").Select
           With ActiveSheet.QueryTables.Add(Connection:= _
                "TEXT;C:\Documents and Settings\wancinlee\Desktop\SOR\SOR_txt" & FileNameCell.Value & ".txt" _
                , Destination:=Range("$A$1"))
                .Name = "SOR_" & FileNameCell.Value
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = 437
                .TextFileStartRow = 1
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = True
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = False
                .TextFileColumnDataTypes = Array(1)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=True
            End With
            
            Sheets("Sheet2").Select
            For i = 2 To 1000
            Range("A7").Copy Destination:=Sheets("Sheet3").Range("B" & i)
            Range("A11").Copy Destination:=Sheets("Sheet3").Range("A" & i)
            Range("A23").Copy Destination:=Sheets("Sheet3").Range("C" & i)
            Range("A24").Copy Destination:=Sheets("Sheet3").Range("D" & i)
            
            Cells.Select
            Range("B6").Activate
            Selection.QueryTable.delete
            Selection.ClearContents
        
    Next FileNameCell

    
End Sub