VBA run-time error '1004' while looping through .csv file

Question

I'm trying to loop through .csv file, opened through FileDialog. The code throws a

run-time '1004' error

on the line:

Set searchInColumn = ActiveSheet.Cells(i, j).Offset(, -1).EntireColumn

In the process, the data from populated columns is being put into cells in the first column, separated by semicolons.

My idea is to run the code on .csv and .xlsx files. Code is shortened. Whatever code is below the problematic line, the whole Sub works on .xlsx, but gets stuck on .csv. It also goes well when I get rid of all the FileDialog stuff, put the code in .xlsb file, paste the data there (from .csv) and run the macro from alt+f8 list.

I run out of ideas to solve this, could you give me some hint?

Sub FixCSV()

    Dim findMatch As Range, searchInColumn As Range
    Dim i As Long, j As Long, k As Long, lastRow As Long, lastColumn As Long
    Dim chosenFile As Integer
    Dim chooseFiles As Office.FileDialog

    Application.ScreenUpdating = False

    Set chooseFiles = Application.FileDialog(msoFileDialogFilePicker)

        With chooseFiles      
            .AllowMultiSelect = True
            .Title = "Please select the file."
            .InitialFileName = "c:\"
            .InitialView = msoFileDialogViewList
        End With

     chosenFile = chooseFiles.Show

    If chosenFile = -1 Then
        For k = 1 To chooseFiles.SelectedItems.Count
            Workbooks.Open chooseFiles.SelectedItems(k)          

            lastColumn = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column           

            j = 2

            Do Until j = lastColumn

                Set searchInColumn = ActiveSheet.Cells(1, j).Offset(, -1).EntireColumn
                Set findMatch = searchInColumn.Find(What:=ActiveSheet.Cells(1, j).Value)

                If ActiveSheet.Cells(1, lastColumn).Offset(0, 1).Value = "" Then                     
                        j = j + 1    
                End If
            Loop
        Next k
End Sub

The first loop j = 0 and there is no column 0, the second loop j = 1 and you try to offset back to column 0. j needs to start at 2. — Scott Craner, Jan 24 '19 at 19:31
Thx for noticing, skipped my attention while shortening the code, I've corrected this. In general, the code works on .xlsx, but not on .csv, and it gets stuck on line that includes range, so maybe it's something with referrencing to objects? And yes, with j starting at 2 it throws the same error, on the same line. — Drzemlik, Jan 24 '19 at 19:45
Explicitly reference your sheets instead of using activesheet. — Luuklag, Jan 24 '19 at 21:02
But then, how to do that if I need the code to run on files with different names. I tried to change ActiveSheet to Workbooks(k).Worksheets(1), as I know the files will always have one sheet, but this throws the same error on line not included in the extract above: If Workbooks(k).Worksheets(1).Cells(i, lastColumn).Offset(0, 1).Value <> "" Then — Drzemlik, Jan 24 '19 at 21:28
Thank you Luuklag for the hint, I think I solved the problem. — Drzemlik, Jan 26 '19 at 22:59

Drzemlik · Answer 1 · 2019-01-26T23:02:48.610

Problem solved, below the corrected code:

Set chooseFiles = Application.FileDialog(msoFileDialogFilePicker)

    With chooseFiles     
        .AllowMultiSelect = True
        .Title = "Please select the file."
        .InitialFileName = "c:\"
        .InitialView = msoFileDialogViewList
        .Filters.Add "All", "*.*"          
    End With

If chooseFiles.Show = -1 Then
    For k = 1 To chooseFiles.SelectedItems.Count

        'defining path+file name
        xlFileName = chooseFiles.SelectedItems(k)
        Workbooks.Open chooseFiles.SelectedItems(k)

        'to reference Worksheets explicitly in order to avoid the run-time 
         '1004' error:
        Set wrk = Workbooks.Open(xlFileName)
        Set Sh = wrk.Worksheets(1)

        'condition in case a file is .csv (splits .csv data into columns according to 
          'separators csv):       
        If InStr(1, wrk.Name, ".csv") Then              
            Sh.Range(Range("A1"), Range("A1").End(xlDown)).TextToColumns _
            DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=True, Comma:=True, Space:=False, Other:=False               
        End If

        lastRow = Sh.Cells(Sh.Rows.Count, "A").End(xlUp).Row
        lastColumn = Sh.Cells(1, Sh.Columns.Count).End(xlToLeft).Column

        i = 2           
        Do Until i = lastRow
           'do sth
        Loop
    Next k
End If

End Sub

Answers that enabled me to solve the problem:

-on dealing with files opened with FileDialog: https://stackoverflow.com/a/21723463/10348607

-on dealing with .csv here: https://stackoverflow.com/a/8526046/10348607

VBA run-time error '1004' while looping through .csv file

1 Answers1

Linked