"End With" error & other VBA code critiques

[sadsadsaxofsht]

Hello,

I am trying to prep a batch of files in a folder for use in a database, so my code (pasted in whole below) attempts to 1) create a number of columns with ID codes for each row, 2) clean up the existing files' field headings, 3) delete unnecessary rows, and 4) compile them all into one worksheet.

I'm a novice to VBA and programming in general, so my main method of developing programs is mostly through copying-and-pasting from google (lmao), which absolutely explains the errors.

The first error (of many to come, I'm sure) is an "end with" error. Any pointers on how to solve that?

Additionally, could someone take the time to point out any glaring inconsistencies or inefficiencies in this code? I know that might be a bit of a tall order, but I would really appreciate the criticism!

Thanks!

Here's the code:

Sub combiningFiles()


'IDing all files
Dim wb As Workbook
Dim strFile As String, strDir As String
Dim headers() As Variant
headers() = Array("Data Number", "Date-Time", "Temperature", "Relative Humidity", "Concentration")
strDir = "C:\Users\..."
strFile = Dir(strDir & "*.xlsx")


Do While strRile <> ""


    Set wb = Workbooks.Open(Filename:=strDir & strFile, Local:=True)
    
    With wb
        'Inserting a column at column i
        Range("I1").EntireColumn.Insert


        'Filling new column with pollutant ID
        Dim LastRow As Long
        LastRow1 = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Range("I3:I" & LastRow).Value = "CO2"
        
        'Inserting a column at column j
        Range("J1").EntireColumn.Insert


        'Filling new column with location ID
        LastRow2 = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Range("J3:J" & LastRow).Value = "E"
        
        'Inserting a column at column j
        Range("K1").EntireColumn.Insert


        'Filling new column with room ID
        LastRow3 = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Range("K3:K" & LastRow).Value = "Living Room"
        
        'Inserting a column at column l
        Range("L1").EntireColumn.Insert


        'Filling new column with home ID
        LastRow4 = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Range("L3:L" & LastRow).Value = "Living Room"
        
        'Inserting a column at column m
        Range("M1").EntireColumn.Insert


        'Filling new column with home ID
        LastRow5 = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Range("M3:M" & LastRow).Value = Mid(FilePath, InStrRev(FilePath, "") + 12, InStrRev(FilePath, ".") - InStrRev(FilePath, "") - 12)


        'Simplify field headings
        .Rows(2).Value = "" 'This will clear out row 2
        For i = LBound(headers()) To UBound(headers())
        .Cells(2, 2 + i).Value = headers(i)
        
        'Delete first row
        Rows(1).Delete
        
        'Compile all files in a folder into one spreadsheet
        Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
        For Each Sheet In ActiveWorkbook.Sheets
        Sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next Sheet
        Workbooks(Filename).Close
        Filename = Dir()
    Set wb = Nothing
    strFile = Dir
    End With
    
Loop


End Sub

 

[Joe4]

Welcome to the Board!

Sometimes those errors messages are a bit misleading. All loops and With statements need to be closed. You are actually missing the closing "Next" statement for your "For i..." loop (you have two "For" statements, but only one "Next" statement).

Note that this things are much easier for us to see if you use Code tags so the indenting and spacing on your code isn't lost when you paste it here.
See: https://www.mrexcel.com/forum/board-announcements/515787-how-post-your-vba-code.html