dimanche 23 septembre 2018

Proceeding to next cell if a condition is met

I have a sheet with Item names in the firs row.

enter image description here

I am using a Forloop to pass trough the cells in row 1 - i. I use the value content of each cell to import a column from a .CSV file in the corresponding cell below it in row 2, by using j for that. However, I have some .CSV files that are missing and I need to move on to the next cell in row 2, while moving into the next cell in row 1. Basically skipping a column.

What I have so far is:

Dim FSO As Object
Dim Folder As Object
Dim File As String

Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder("C:\Users\Betty\AppData\Roaming\MetaQuotes\Terminal\B4D9BCD10BE9B5248AFCB2BE2411BA10\MQL4\Files")

For i = 2 To HCP.Cells(1, HCP.Columns.Count).End(xlToLeft).Column

Item = HCP.Cells(1, i).Value

FilePath = Folder & "\" & Item & "1440.CSV"

    If Item = "" Or Dir(FilePath) = "" Then GoTo Continue
        j = HCP.Cells(2, HCP.Columns.Count).End(xlToLeft).Column
            With HCP.QueryTables.Add(Connection:="TEXT;" & FilePath, Destination:=HCP.Cells(2, j + 1))
                .TextFileParseType = xlDelimited
                .TextFileCommaDelimiter = True
                .TextFileSpaceDelimiter = True
                .TextFileColumnDataTypes = Array(9, 9, 9, 9, 9, 1, 9, 9, 9, 9)
                .Refresh BackgroundQuery:=False
            End With

Continue:

Next 

I need the column index of j to be corresponding to the column index of i at all times.

Aucun commentaire:

Enregistrer un commentaire