do action inside a for IF i match a condition

[idnob]

Hi all guys!
I need to do actions inside a for only i match a particulat condition, a genral idea is:

For Each FileItem In SourceFolder.Files
               IF i<96 then ...

To be more specific i have to :
- take first 96 files inside a folder (this part work)
- for each file i take from the folder i have to do some operations (this part works)

My code:

Sub autoText()
End Sub
Sub auto2()
'
' auto2 Macro
'
    Dim lastRow As Long
    Dim sFORMULA As String
    Dim x As Double
    Dim vERROR As Variant
    Dim y As Long
    Dim rng1 As Range, rng2 As Range
    Dim strMidString As String
'count number of files
    Dim strDir As String
    Dim FSO As Object
    Dim objFiles As Object
    Dim obj As Object
    Dim lngFileCount As Long
    Dim c As Integer
    
    strDir = "\Documents\files"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Set objFiles = FSO.GetFolder(strDir).Files
    
    lngFileCount = objFiles.Count
    
'check the first name in the folder
    Dim FSO2 As Object
    Dim SourceFolder As Object
    Dim FileItem As Object
    Dim FileItemToUse As Object
    Dim SourceFolderName As String
    Dim i As Long
    SourceFolderName = "\Documents\files" & FileItem.Name _
            , Destination:=Range("$A$1"))
    Set FSO2 = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    For Each FileItem In SourceFolder.Files
        If i = 0 Then
            Set FileItemToUse = FileItem
            Exit For
        End If
    Next FileItem

Dim z As Integer
'import all the file in the folder
z = 1
    For Each FileItem In SourceFolder.Files
         
  [B] 'i need to do the following lines only if i match the condition i<96
[/B]        Sheets.Add.Name = FileItem.Name         'add a new sheet for a new file
            'file import
            [B]With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;\Documents\files\" & FileItem.Name _
            , Destination:=Range("$A$1"))
[/B]        .Name = FileItem.Name
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 1252
            .TextFileStartRow = 15
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = True
            .TextFileColumnDataTypes = Array(9, 9, 1, 9, 9, 1, 9, 9, 9, 9, 1, 9, 9, 9)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
            End With

' clean column from blank cells
    Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    lastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
' clean column from non numeric value
  Application.ScreenUpdating = False
    x = Range("B" & Rows.Count).End(xlUp).Row
    For Each rng1 In Range("B2:B" & x)
        If Not IsNumeric(rng1.Value) Or LenB(rng1) = 0 Then
         If rng2 Is Nothing Then
            Set rng2 = rng1
         Else
            Set rng2 = Union(rng2, rng1)
         End If
        End If
    Next rng1
    
If Not rng2 Is Nothing Then
    rng2.EntireRow.Delete
    Set rng2 = Nothing
End If
Application.ScreenUpdating = True

   
    
' add headers
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Application"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Max licenses"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "In use"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Time"
    Range("D2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
'put timestamp in the yellow cell
    strMidString = Mid(FileItem.Name, 12, 3)
    Selection.NumberFormat = "0.00"
    Cells(4, 2).Value = strMidString
    
[B]'close if[/B]
    
End Sub

If i put "if" after the "for" i have to put ":" after every instruction and, if i put ":" after the line where i get the source file (in bold) the script is not working because of a syntax error, i cannot even put the 3 lines on a unique line!
Ideas?
Thank you very much in advance!

 

[Andrew Poulsom]

It sounds like you forgot the End If.

 

[idnob]

Not really, because i didn't open it yet, because if i open it i should put ":" at the end of every line in the code and i cannot put it after everyone of these lines:

[B]With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;\Documents\files\" & FileItem.Name _
            , Destination:=Range("$A$1"))
[/B]

And i cannot also put them on the same line.

 

[Andrew Poulsom]

Your code doesn't compile - you are missing Next FileItem and you have an extra closing parenthesis here:

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;\Documents\files\" & FileItem.Name _
    , Destination:=Range("$A$1"))

 

[idnob]

So nice of you to help me...
without the if i want to put everything is working.
I cannot understad why vba doesn't provide a way to write something like:

if condition then {
code code code
}

 

[Andrew Poulsom]

You need:

If Condition Then
'   Code
'   Code
'   Code
End If

 

[idnob]

exactly but in VBA

if condition then
'       code[B]:[/B]
'       code[B]:[/B]
'       code[B]:[/B]
end if

I cannot undeestand why i cannot put the : after

With ActiveSheet.QueryTables.Add(Connection:= _    "TEXT;\Documents\files\" & FileItem.Name _    , Destination:=Range("$A$1")[COLOR=#ff0000][B])[/B][/COLOR]</PRE>

 

[Andrew Poulsom]

As I said before your code doesn't compile because you have an extra closing parenthesis which I highlighted in red. Try removing it.

 

[idnob]

the code compile i don't think that is the matter

 

[Andrew Poulsom]

The code you posted does not compile. If you want more help post some code that does compile.